mysql load data local infile从客户端装入表

郑全

平时我们使用 select into outfile ，load data infile方式，都是从服务器上装入表数据的，如果我们想在客户端把一个文件的数据装入数据库，该怎么办呢

 

比如，我的服务器ip是 192.168.0.15 ，操作系统为 linux,客户端为win7,

 

登录到服务器

c:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root -p -h 192.168.0.15
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.23-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>

 

下面进入到sztech库

mysql> use sztech

 

查看一下表结构：

 

mysql> desc emp_bak1
    -> ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| empid     | int(11)     | YES  |     | NULL    |       |
| last_name | varchar(20) | YES  |     | NULL    |       |
| salary    | double      | YES  |     | NULL    |       |
| hire_date | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

 

mysql> select * from emp_bak1;
Empty set (0.00 sec)

 

 

 

d:\emp2.sql内容如下：

 

100,smitty,5000,2015-04-05
200,\N,\N,\N
300,\N,\N,\N

 

把 d:\emp2.sql文件 导入到该表

 

mysql> load data infile 'd:\emp2.sql' into table emp_bak1 fields terminated by '
,' ;
ERROR 13 (HY000): Can't get stat of '/usr/local/mysql/data/sztech/d:emp2.sql' (E
rrcode: 2 - No such file or directory)

 

不加 local直接导入，去找服务器上的文件，加上 local后，问题搞定：

 

mysql> load data local infile 'd:\emp2.sql' into table emp_bak1 fields terminate
d by ',' ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

 

验证一下：

mysql> select * from emp_bak1;
+-------+-----------+--------+------------+
| empid | last_name | salary | hire_date  |
+-------+-----------+--------+------------+
|   100 | smitty    |   5000 | 2015-04-05 |
|   200 | NULL      |   NULL | NULL       |
|   300 | NULL      |   NULL | NULL       |
+-------+-----------+--------+------------+
3 rows in set (0.00 sec)

mysql>

 

问题搞定。