- 导出csv
SELECT * FROM [table] INTO OUTFILE '[filename]'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
- 导入csv
LOAD DATA LOCAL INFILE '[filename]' INTO TABLE [table] ('[field1]','[field2]','[field3]')
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\n';
-> IGNORE 1 ROWS;
IGNORE 1 ROWS: 当csv包含表头时使用
当csv字段顺序和数据卡schema不同时可在表后列出字段顺序
-
导入数据权限问题
导入数据时如果数据库没有设置local_infile=1，会遇上权限问题：
LOAD DATA LOCAL INFILE '*.csv' INTO TABLE data_collector_androiddevice IGNORE 1 ROWS Error Code: 1148. The used command is not allowed with this MySQL version 0.00023 sec
运行Mysql命令：
SHOW VARIABLES LIKE 'local_infile';
可以看到local_infile是off
修改local_infile：
SET GLOBAL local_infile = 1;
