MySQL-导入导出excel文件（csv文件）

一. 导入Excel文件

1. 将 Excel 转成 CSV 文件, 点击 Excel 的“文件”-“另存为”，然后把保存类型换成 CSV
2. 通过记事本打开新生成的 CSV 文件，点击“文件”-“另存为”，将编码改成 UTF-8
3. 在 MySQL 中创建相应表
4. 导入数据
   登录: mysql -uroot -p --local-infile=1
   执行: set global local_infile = 1;
   导入数据: load data local infile '/tmp/student_score.csv' into table student_score fields terminated by ',' ignore 1 lines;
   如果正常，会显示: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
   在实战中，如果 Warnings 后面的数字不是 0，需要注意了，可执行下面语句查看是什么原因: show warnings;

二. 导出csv文件

select * from world.city into outfile '/tmp/world_city.csv' fields terminated by ',';

mysql导出csv：
SELECT * FROM test_info INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

test_info INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ','　-- 字段间以,号分隔
OPTIONALLY ENCLOSED BY '"'　 -- 字段用"号括起
ESCAPED BY '"' 　　　　　　   -- 字段中使用的转义符为"
LINES TERMINATED BY '\r\n' 　-- 行以\r\n结束

mysql导入csv：
LOAD DATA INFILE '/tmp/test.csv' INTO TABLE test_info FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';