介绍
LOAD DATA INFILE语句以非常高的速度将文本文件中的行读入表中。 LOAD DATA INFILE是SELECT … INTO OUTFILE的补充。要将表中的数据写入文件,请使用SELECT … INTO OUTFILE。 要将文件读回表中,请使用LOAD DATA INFILE。
基本语法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},
[, col_name={expr | DEFAULT}] ...]
-
LOW_PRIORITY | CONCURRENT:若指定LOW_PRIORITY,则该LOAD DATA 语句的真正执行将推迟到没有客户端在读取所设计的表时(只对只支持表锁的引擎有效);
-
LOCAL:若未指定该关键字,则说明文件在MySQL所在机子上,文件由MySQL服务器去读取,此时如果后面指定为文件路径为相对路径,1.如果路径以./开头,那么路径是相对于MySQL的data目录的,2.如果路径不是以./开头,那么路径是相对于默认数据库的目录的;若指定了该关键字,则说明文件在客户端机子上,文件由客户端去读取并通过网络发送给MySQL服务器
-
REPLACE | IGNORE :当插入的行遇到UNIQUE字段重复时,若指定为REPLACE,则用该行替换原来的行;若指定为IGNORE,则忽略改行
-
PARTITION (partition_name,…):将数据插入指定分区
-
CHARACTER SET:若不指定字符集,MySQL默认使用character_set_database变量指定的字符集去读取文件,若文件字符集不同,则应指定该关键字
-
FIELDS TERMINATED BY:字段值的分隔符,若不指定则默认为 ‘\t’
-
FIELDS ENCLOSED BY:字段值的包含字符,若不指定则默认为 ‘’
-
FIELDS ESCAPED BY:字段值的转义字符,若不指定则默认为’\’
-
LINES TERMINATED BY:指定行分隔符,若不指定则默认为为系统的默认行分隔符(‘\r\n‘ on windows,’\n’ on linux)
-
LINES STARTING BY:若指定该值为xxx,则MySQL会自动去掉xxx及其前面的字符,若某行不包含xxx,则改行将被忽略,若不指定默认为’’,比如执行以下语句:
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
对应的文件内容为:
xxx"abc",1 something xxx"def",2 "ghi",3
则实际插入的行为(“abc”, 1), (“def”, 2)
-
IGNORE LINES | ROWS:忽略文件开头的指定行,比如指定为2,那么MySQL只会解析并插入第三行及后面的数据
操作
1 修改参数
由于导入大量数据可能比较占用系统资源,所以应该先修改参数,保证复制正确快速的运行。由于InnoDB涉及事务所以为了优化插入先将表改为MyISAM然后在转回InnoDB,这样操作效率较高。
# 关闭binlog
mysql> SET SQL_LOG_BIN = 0;
# 关闭唯一性检查
mysql> set unique_checks=0;
# 关闭外键检查
mysql> set foreign_key_checks=0;
# 调整MyISAM引擎参数
mysql> SET SESSION BULK_INSERT_BUFFER_SIZE=256217728;
mysql> SET SESSION MYISAM_SORT_BUFFER_SIZE=256217728;
mysql> SET GLOBAL KEY_BUFFER_SIZE=256217728;
mysql> USE DATABASE;
mysql> ALTER TABLE {table} ENGINE=MyISAM;
# 导入指令
mysql> LOAD DATA INFILE '/data.txt' INTO TABLE {table} FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
# 导入完毕后进行恢复
mysql> SET SQL_LOG_BIN = 1;
mysql> set unique_checks=1;
mysql> set foreign_key_checks=1;
mysql> ALTER TABLE {table} ENGINE=InnoDB;
报错信息:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
这个报错信息是由于MySQL权限问题指定了固定目录执行导入导出指令,执行如下指令找到当前的路径设置
show variables like '%secure%';
然后修改导入指令
mysql> LOAD DATA INFILE '/var/lib/mysql-files/data.txt' INTO TABLE {table} FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;