首先查看表结构:
mysql> desc user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| pwd | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.52 sec)
其次构建数据到文件中:
111 wangchy1 name
222 wangchy2 name
然后运行语句:
mysql> load data local infile 'f:/test/user.txt' into table user lines terminated by '\r\n';
Query OK, 2 rows affected (0.23 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
最后查看成功插入。
如果某个字段为空,则用\N代替。
默认每个字段的分隔符为tab键,如果修改成其他的需要加个语句,本语句以分隔符为,举例说明:
mysql> load data local infile 'f:/test/user.txt' into table user fields terminated by ',' lines terminated by '\r\n';
Query OK, 2 rows affected (0.13 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0