mysql的load data,高速将文本文件,插入数据库中
1语法
LOAD DATA [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'file_name.txt'
[ REPLACE | IGNORE ]
INTO TABLE tbl_name
[ FIELDS
[ TERMINATED BY 'string' ]
[ [OPTIONALLY] ENCLOSED BY 'char' ]
[ ESCAPED BY 'char' ]
]
[ LINES
[ STARTING BY 'string' ]
[ TERMINATED BY 'string' ]
]
[ IGNORE number LINES ]
[ (col_name_or_user_var,...) ]
[ SET col_name = expr,...] ]
关键字解释
LOAD DATA [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'file_name.txt'
[ REPLACE | IGNORE ]
INTO TABLE tbl_name
1LOW_PRIORITY: 加载数据被延迟，直道没有其他客户端从表中读取为止
CONCURRENT: 当加载的数据正在进行的时，如果有用户请求则开启新的线程来获取数据
2如果指定了LOCAL 关键字，它将对连接的客户端做出解释：
• 如果指定了LOCAL，客户端主机上的客户端组件读取文件并发送到服务器。可以给出文件的完整路径以确定其精确位置。如果给出的是相对路径，则文件名是相对于客户端组件启动时所在的目录。
• 如果没有指定LOCAL，文件是位于服务器的主机上，并且直接被服务器读取。
当从服务器主机定位文件时，服务器使用下列规则：
• 如果给定完整的路径，服务器使用该路径名。
• 如果给定一个或多个前置构件的相对路径，服务器以相对服务器的数据目录搜索文件。
• 如果给定没有前置构件的文件名，服务器从当前数据库的数据库目录搜寻文件。
3REPLACE 和IGNORE 关键字处理那些与已存在的主键值重复的输入记录
如果指定了REPLACE，输入行将会代替已存在的行（也就是说，主索引值相同的行将作为存在的行）。参考REPLACE 语法 。
如果指定了IGNORE，与已存在行主键值重复的输入行将被跳过。如果不指定二者中的任一个，则操作行为将依赖是否指定了LOCAL关键字。
没有指定LOCAL，则如果发现有重复的键值，将产生一个错误，并忽略文本文件的其余部分。
如果指定了LOCAL，则缺省的操作行为将与指定了IGNORE 的相同；这是因为，在操作过程中，服务器没有办法终止文件的传送。
[ FIELDS
[ TERMINATED BY 'string' ]
[ [OPTIONALLY] ENCLOSED BY 'char' ] //enclosed by描述的是字段的括起字符。
[ ESCAPED BY 'char' ] //escaped by描述的转义字符。默认的是反斜杠 （backslash：\ ）
]
[ LINES
[ STARTING BY 'string' ]
[ TERMINATED BY 'string' ]
]
4
数据格式:
"中华之子","100","威武的不行了"
"大米","63","我爱吃"
"苹果","15","好吃"
load data infile 'D:/aa.txt' ignore into table 表名 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\r\n' (表中的字段1,表中的字段2,表中的字段3);
terminated by ',' 用什么风格字符
enclosed by '"' 字符用什么包起来的
lines terminated by '\r\n' 行的末尾换行符是什么(liunx改为\n)
[ IGNORE number LINES ]
[ (col_name_or_user_var,...) ]
[ SET col_name = expr,...] ]
5IGNORE number LINES选项可被用来忽略在文件的某一行
假设数据文件如下：
Book1.csv
编号,名称,说明
1,测试数据1,"测试CSV文件中,有逗号"
2,测试数据2,"测试CSV文件中有""双引号"""
3,测试数据3,"测试CSV文件中,有逗号和""双引号"""
4,测试数据4,普通数据
mysql> CREATE TABLE Test_Book1 (
-> id int,
-> name VARCHAR(10),
-> data VARCHAR(100)
-> );
Query OK, 0 rows affected (0.05 sec)
下面的 lines terminated by '\r\n' 是 要求换行符号,为 windows的换行
下面的 ignore 1 lines是 忽略第一行的标题行。
mysql> LOAD DATA INFILE 'f:/Book1.csv'
-> INTO TABLE Test_Book1
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> lines terminated by '\r\n'
-> ignore 1 lines
-> (id, name, data);
mysql> select * from test_book1;
+------+-----------+--------------------------------+
| id | name | data |
+------+-----------+--------------------------------+
| 1 | 测试数据1 | 测试CSV文件中,有逗号 |
| 2 | 测试数据2 | 测试CSV文件中有"双引号" |
| 3 | 测试数据3 | 测试CSV文件中,有逗号和"双引号" |
| 4 | 测试数据4 | 普通数据 |
+------+-----------+--------------------------------+
ignore 1 lines 忽略第一行的标题行。
6@dummy的使用
假设数据文件如下：
Book1.csv
1,测试数据1,"测试CSV文件中,有逗号"
2,测试数据2,"测试CSV文件中有""双引号"""
3,测试数据3,"测试CSV文件中,有逗号和""双引号"""
4,测试数据4,普通数据
mysql> CREATE TABLE Test_Book1 (
-> id int,
-> name VARCHAR(10),
-> data VARCHAR(100)
-> );
Query OK, 0 rows affected (0.05 sec)
我只需要第一,二列的数据的时候
mysql> LOAD DATA INFILE 'f:/test_main.txt'
-> INTO TABLE test_main6
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY ''''
-> (id,name, @dummy);
mysql> select * from test_book1;
+------+-----------+--------------------------------+
| id | name | data |
+------+-----------+--------------------------------+
| 1 | 测试数据1 | |
| 2 | 测试数据2 | |
| 3 | 测试数据3 | |
| 4 | 测试数据4 | |
+------+-----------+--------------------------------+
这里的关键点， 在于那个 @dummy。
对你而言， 如果文件有 10列， 你只导 2 ， 7 的话， 那就是
@dummy,列2,@dummy,@dummy,@dummy,@dummy,列7,@dummy,@dummy,@dummy
7set子句
SET 子句可以提供不是来源于输入文件的值。下面的语句将column3 设置为当前的日期和时间：
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, column2)
SET column3 = CURRENT_TIMESTAMP;
可以让某一列的值参与一些操作
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = @var1/100;
也可以让前后列的数据产生关系(假设第一列是一个日期格式的数据,我的第三列可以根据第一列的日期计算出星期信息然后存入数据库)
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, column2)
SET column3 = SET name=CASE WHEN DATE_FORMAT(date,'%w') =1 THEN "星期一"
WHEN DATE_FORMAT(date,'%w') =2 THEN "星期二"
end;
使用列/变量列表和SET子句要受到一下限制：
• SET 子句中的赋值列名应当只能在赋值操作符的左边。
• 在SET 赋值语句中，可以使用子查询。此查询返回一个将被赋予列的值，它可能仅仅是一个标量查询。不能用子查询去查询将被导入的表。
• 对于列/变量列表或SET 子句，由于使用IGNORE 子句而被忽略的行就不会被处理。
• 由于用户变量没有显示宽度，当导入的数据是行固定格式时，将不能使用用户变量。
8设置存入时候使用的字符
gbk utf8
load data infile 'C:\\Users\\edgewalk\\Desktop\\test.txt' ignore into table test character set gbk fields terminated by ','
lines terminated by '\n' (
date,
money,
desctipt
)
2使用load data infile导入导出备份数据
如果要导出一个表中的部分字段或者部分符合条件的记录，需要用到了mysql的into outfile 和 load data infile 。 例如下面的mysql命令是把select的mytable表中的数据导出到C:\\Users\\7q\\Desktop\\test1.txt。
select * from test into outfile 'C:\\Users\\7q\\Desktop\\test1.txt' fields terminated by ',' lines terminated by '\r\n' ;
假如要导入刚才备份的数据，可以使用load file方法，例如下面的mysql命令，把导出的数据导入了mytable_bak的表中：
load data infile 'C:\\Users\\7q\\Desktop\\test1.txt' into table mytable_bak fields terminated by ',' lines terminated by '\r\n';
这种方法的好处是，导出的数据可以自己规定格式，并且导出的是纯数据，不存在建表信息，你可以直接导入另外一个同数据库的不同表中.
