MariaDB中快速导入导出数据

表数据量比较大的时候快速导入导出数据

涉及到两个命令：

SELECT * INTO OUTFILE

LOAD DATA INFILE

如：从tb_test表导出数据，然后导入到表tb_test2中

SELECT * INTO OUTFILE '/home/tmp/test.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM tb_test;

LOAD DATA INFILE '/home/tmp/test.txt' 
IGNORE INTO TABLE tb_test2
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

需要注意：LOAD DATA INFILE如果想使用Local关键字从本地导入文件，需要在环境变量中设置local_infile设置值为1，默认是不可用的

执行期间如果引擎支持ALTER TABLE... DISABLE KEYS则会自动禁用索引以加快速度

官方文档说明：

https://mariadb.com/kb/en/library/select-into-outfile/

https://mariadb.com/kb/en/library/load-data-infile/