有时需要把 MySQL 的数据导出为 CSV 格式的文件便于分析和传输，有的时候需要把 CSV 格式的内容导入到 MySQL，MySQL 支持对 CSV 格式的文件导入和导出。
MySQL 导出 CSV
1
2
3
4
5
| SELECT * FROM demo WHERE id>2 ORDER BY id
INTO OUTFILE '/Users/Biao/Desktop/a.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
|
MySQL 导入 CSV
1
2
3
4
5
6
| LOAD DATA INFILE '/Users/Biao/Desktop/a.csv'
INTO TABLE demo
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n';
|
还可以在 () 部分指定导入的列:
1
2
3
4
5
6
7
| LOAD DATA INFILE '/Users/Biao/Desktop/a.csv'
INTO TABLE demo
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
(id, description)
|
