load data infile into table 的使用例子

最新推荐文章于 2024-09-18 09:54:55 发布

qq_41560351

最新推荐文章于 2024-09-18 09:54:55 发布

阅读量2.2k

点赞数

文章标签： mysql

本文详细介绍如何使用MySQL的LOAD DATA INFILE与SELECT INTO OUTFILE语句进行数据的批量导入导出操作。通过具体示例展示了如何指定字符集、字段分隔符及包围符等参数，确保数据正确加载到数据库表中或从表中导出。

摘要由CSDN通过智能技术生成

原文来自：http://blog.sina.com.cn/s/blog_605f5b4f0100zoqb.html

常用如下：

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 | 的确是好