【导入导出】MySQL -Load Data-Select into outfile.md

• FIELDS TERMINATED BY ',' 分隔符为逗号
• OPTIONALLY ENCLOSED BY '" 逃逸字符为双引号，也就是每个值都用"包含起来
• ignore 1 lines 忽略第一行，一般都是标题

LOAD DATA

LOAD DATA LOCAL INFILE '/root/testaaa.csv' INTO TABLE testaaa FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ignore 1 lines;

指定目标字段，字段放最后

LOAD DATA LOCAL INFILE '/root/qx/testaaa.csv' INTO TABLE testaaa FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ignore 1 lines (USER_ID,APP_ID,CERT_NO,MOBILE,REAL_NAME,IS_CERTIFIED,USER_STATUS,CREATE_DATE,UPDATE_TIME,SOURCE);

SELECT INTO OUTFILE

• 需要 secure_file_priv = ""，默认为 NULL
• 需要有一个文件夹所属是mysql:mysql

mkdir /mysqlout
chown mysql.mysql /mysqlout

mysql> SELECT * FROM testbbb INTO OUTFILE "/mysqlout/testbbb.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';