mysql load into table_MySQL load data infile into table 的使用

常用如下：

Load Data InFile 'C:/Data.txt' Into Table `TableTest` Lines Terminated By '\r\n';

这个语句，字段默认用制表符隔开，每条记录用换行符隔开，在Windows下换行符为“\r\n”

C:/Data.txt 文件内容如下面两行：

1 A

2 B

“1”和“A”之间有一个制表符

这样就导进两条记录了。

自定义语法

Load Data InFile 'C:/Data.txt' Into Table `TableTest` Fields Terminated By ',' Enclosed By '"' Escaped By '"' Lines Terminated By '\r\n';

Fields Terminated By ',' Enclosed By '"' Escaped By '"'

表示每个字段用逗号分开，内容包含在双引号内

Lines Terminated By '\r\n';

表示每条数据用换行符分开

和 Load Data InFile 相反的是

Select * From `TableTest` Into OutFile 'C:/Data_OutFile.txt';

把表的数据导出

我们的示例文本文件：

"我爱你","20","相貌平常，经常耍流氓！哈哈"

"李奎","21","相貌平常，经常耍流氓！哈哈"

"王二米","20","相貌平常，经常耍流氓！哈哈"

"老三","24","很强"

"老四","34","XXXXX"

"老五","52","***%*￥*￥*￥*￥"

"小猫","45","中间省略。。。"

"小狗","12","就会叫"

"小妹","21","PP的很"

"小坏蛋","52","表里不一"

"上帝他爷","96","非常英俊"

"MM来了","10","。。。"

"歌颂党","20","社会主义好"

"人民好","20","的确是好"

"老高","10","学习很好"

"斜三","60","眼睛斜了"

"中华之子","100","威武的不行了"

"大米","63","我爱吃"

"苹果","15","好吃"

我们的示例表结构：

CREATE TABLE t0 (

id bigint(20) unsigned NOT NULL auto_increment,

name char(20) NOT NULL,

age tinyint(3) unsigned NOT NULL,

description text NOT NULL,

PRIMARY KEY (id),

UNIQUE KEY idx_name (name)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> load data infile '/tmp/t0.txt' ignore into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n'(name,age,description);

Query OK, 19 rows affected (0.01 sec)

Records: 19 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t0;

+----+----------+-----+----------------------------+

| id | name     | age | description                |

+----+----------+-----+----------------------------+

| 1 | 我爱你   | 20 | 相貌平常，经常耍流氓！哈哈 |

| 2 | 李奎     | 21 | 相貌平常，经常耍流氓！哈哈 |

| 3 | 王二米   | 20 | 相貌平常，经常耍流氓！哈哈 |

| 4 | 老三     | 24 | 很强                       |

| 5 | 老四     | 34 | XXXXX                      |

| 6 | 老五     | 52 | ***%*￥*￥*￥*￥           |

| 7 | 小猫     | 45 | 中间省略。。。             |

| 8 | 小狗     | 12 | 就会叫                     |

| 9 | 小妹     | 21 | PP的很                     |

| 10 | 小坏蛋   | 52 | 表里不一                   |

| 11 | 上帝他爷 | 96 | 非常英俊                   |

| 12 | MM来了   | 10 | 。。。                     |

| 13 | 歌颂党   | 20 | 社会主义好                 |

| 14 | 人民好   | 20 | 的确是好                   |

| 15 | 老高     | 10 | 学习很好                   |

| 16 | 斜三     | 60 | 眼睛斜了                   |

| 17 | 中华之子 | 100 | 威武的不行了               |

| 18 | 大米     | 63 | 我爱吃                     |

| 19 | 苹果     | 15 | 好吃                       |

+----+----------+-----+----------------------------+

19 rows in set (0.00 sec)

我来说明一下相关的参数

关于我的导入语句，我现在直说两个，其他的参考手册。

character set gbk;

这个字符集一定要写，要不然就会乱码或者只导入一部分数据。

ignore into table

因为name 列加了唯一索引，加这个是为了避免重复数据插入报错。

加入我们再次运行这个导入语句就会发现

Query OK, 0 rows affected (0.00 sec)

Records: 19 Deleted: 0 Skipped: 19 Warnings: 0

没有任何值导入，因为里面已经有了相同的值。

这里也可以用replace into table

MySQL会把相同的先干掉，再插入新的值。

mysql> load data infile '/tmp/t0.txt' replace into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (name,age,description);

Query OK, 38 rows affected (0.00 sec)

Records: 19 Deleted: 19 Skipped: 0 Warnings: 0

mysql> select * from t0;

+----+----------+-----+----------------------------+

| id | name     | age | description                |

+----+----------+-----+----------------------------+

| 20 | 我爱你   | 20 | 相貌平常，经常耍流氓！哈哈 |

| 21 | 李奎     | 21 | 相貌平常，经常耍流氓！哈哈 |

| 22 | 王二米   | 20 | 相貌平常，经常耍流氓！哈哈 |

| 23 | 老三     | 24 | 很强                       |

| 24 | 老四     | 34 | XXXXX                      |

| 25 | 老五     | 52 | ***%*￥*￥*￥*￥           |

| 26 | 小猫     | 45 | 中间省略。。。             |

| 27 | 小狗     | 12 | 就会叫                     |

| 28 | 小妹     | 21 | PP的很                     |

| 29 | 小坏蛋   | 52 | 表里不一                   |

| 30 | 上帝他爷 | 96 | 非常英俊                   |

| 31 | MM来了   | 10 | 。。。                     |

| 32 | 歌颂党   | 20 | 社会主义好                 |

| 33 | 人民好   | 20 | 的确是好                   |

| 34 | 老高     | 10 | 学习很好                   |

| 35 | 斜三     | 60 | 眼睛斜了                   |

| 36 | 中华之子 | 100 | 威武的不行了               |

| 37 | 大米     | 63 | 我爱吃                     |

| 38 | 苹果     | 15 | 好吃                       |

+----+----------+-----+----------------------------+

19 rows in set (0.00 sec)

(name,age,description);这些也就是具体的表属性了，指明这个就可以导入想要的数据。