制造大量数据时使用文本文件导入导出可以大大提升工作效率.

表USERS

mysql> select * from users ;

+----+-------+---------+------+
| id | name | address | flag |
+----+-------+---------+------+
| 1 | named | beijing | 1 |
| 2 | named | beijing | 2 |
| 3 | named | beijing | 3 |
| 4 | named | beijing | 4 |
| 5 | named | beijing | 5 |
| 6 | named | beijing | 6 |
| 7 | named | beijing | 7 |
| 8 | named | beijing | 8 |
| 9 | named | beijing | 9 |
| 10 | named | beijing | 10 |
+----+-------+---------+------+
10 rows in set (0.00 sec)

一 导出数据

1.mysql > select id,name,address from users limit 5 into outfile '/data/users.txt' fields terminated by ',' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\n';

Query OK, 10 rows affected (0.00 sec)

2.# more /data/users.txt

1,named,beijing
2,named,beijing
3,named,beijing
4,named,beijing
5,named,beijing

二 导入数据

1.mysql > load data infile '/data/users.txt' into table user1 fields terminated by ',' lines terminated by ' ' (id,name,address) OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\n';

Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

2.mysql> select * from user1;
+----+-------+---------+------+
| id | name | address | flag |
+----+-------+---------+------+
| 1 | named | beijing | NULL |
| 2 | named | beijing | NULL |
| 3 | named | beijing | NULL |
| 4 | named | beijing | NULL |
| 5 | named | beijing | NULL |
+----+-------+---------+------+
10 rows in set (0.00 sec)

FIELDS TERMINATED BY 大多使用逗号( , ) 竖线( | )分割.

注:导入导出时FIELDS TERMINATED BY 要统一.