- 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 '"';
