常用如下：
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","好吃"
我们的示例表结构：
unsigned NOT NULL auto_increment,
NOT NULL,
unsigned NOT NULL,
text NOT NULL,
(id),
idx_name (name)
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 |
老五