mysql csv导入导出_MySQL 导入导出 CSV 文件

导入

示例：

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

常用参数：

FIELDS TERMINATED BY ','：指定字段分隔符

OPTIONALLY ENCLOSED BY '"'：认为双引号中的是一个独立的字段。Excel 转 CSV 时，有特殊字符(逗号、顿号等)的字段，会自动用双引号引起来

LINES TERMINATED BY '\n'：指定行分隔符，注意，在 Windows 平台上创建的文件，分隔符是 '\r\n'

导出

SELECT … INTO 语法可以将查询结果保存到变量或文件中：

SELECT ... INTO var_list # 将字段的值保存到变量中

SELECT ... INTO OUTFILE # 将选中的行保存到文件中。可以指定列和行的结束符，以生成指定格式的文件。

SELECT ... INTO DUMPFILE # 将一个单独的行写入文件中，没有格式

示例：

mysql > SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

FROM test_table;

mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/1.txt'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

FROM professions;

Query OK, 2220 rows affected (0.04 sec)

常用参数(跟 LOAD DATA INFILE 语法一样)：

FIELDS TERMINATED BY ','：指定字段分隔符

OPTIONALLY ENCLOSED BY '"'：认为双引号中的是一个独立的字段。Excel 转 CSV 时，有特殊字符(逗号、顿号等)的字段，会自动用双引号引起来

LINES TERMINATED BY '\n'：指定行分隔符，注意，在 Windows 平台上创建的文件，分隔符是 '\r\n'

清空表中的所有数据

操作失误时，需要简单的方式清空表，有两种方式：不带 where 参数的 delete 语句，或 truncate。

delete from my_table;

truncate table my_table;

truncate 相当于使用表的结构重新创建表，所有的状态都相当于新表。

不带 where 参数的 delete 则是将表中所有记录一条一条删除。

truncate 比 delete 快，但 truncate 删除后不记录日志，数据无法恢复。

注意事项

Windows 中用 Excel 导出的 CSV 文件，采用 UTF-8-BOM 编码，换行符是 CRLF 回车换行。但是在 Linux 中不支持 UTF-8-BOM 编码，需要手动转为 UTF-8 编码。如果换行符用 CRLF 回车换行，则需要在 LOAD DATA INFILE 命令中通过 LINES TERMINATED BY '\r\n' 明确指定换行符：

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

如果已经转为 LF 换行了，则可以省略这一句。

常见问题

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这一般是因为，你上传或下载的文件没有在 MySQL 指定的安全目录中。默认情况下，MySQL 导入导出文件时，只能使用安全目录。使用下面的命令查看 MySQL 的安全目录：

mysql> SHOW VARIABLES LIKE "secure_file_priv";

+--------------------------+-----------------------+

| Variable_name | Value |

+--------------------------+-----------------------+

| require_secure_transport | OFF |

| secure_auth | ON |

| secure_file_priv | /var/lib/mysql-files/ |

+--------------------------+-----------------------+

3 rows in set (0.00 sec)

解决方案有两个：

将文件复制到 secure_file_priv 指定的安全目录。推荐。

在 /etc/my.cnf 配置文件(Windows 平台下是 my.ini)中关闭 secure_file_priv。这个选项无法动态配置，修改后必须重启。

对于第一个方案，复制文件到安全目录后，需要使用完整路径：

mysql> load data infile '/var/lib/mysql-files/profession.csv' into table professionss fields terminated by ',' lines terminated by '\n';

数据中的第一个字段始终报错

如果是整数，则报错如下：

ERROR 1366 (HY000): Incorrect integer value: '1800' for column 'CompanyID' at row 1

Windows 平台下创建的文件基本上都使用了 BOM 头，即在文件的头部添加描述性信息，可以参考 这里。这会在 Linux 平台下导致致命错误，去掉这个 BOM 头就好了。

Excel 另存为 CSV 文件后，默认编码是“使用 UTF-8 BOM 编码”，在 Notepad++ 中打开文件，选择“使用 UTF-8 编码”保存即可清除 BOM 头：

部分数据保存失败，且有异常数据

数据中出现了双引号，且部分数据插入失败，并插入了部分异常数据：

Excel 另存为 CSV 文件后，对于特殊字符(逗号、顿号等)的字段，会自动用双引号引起来。但是添加的双引号的位置竟然会出错：

1800,1,3,2021304,202,"计算机系统分析技术人员

",1

1800,1,3,2021305,202,"维护工程师

",2

1800,1,3,2021306,202,"销售工程师

",1

可以看到，好多行发生了不应该的换行。两个解决方案：

使用 OPTIONALLY ENCLOSED BY '"' 自动处理，推荐：

mysql> LOAD DATA INFILE '/var/lib/mysql-files/profession.csv' INTO TABLE professionss FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Query OK, 2220 rows affected (0.06 sec)

Records: 2220 Deleted: 0 Skipped: 0 Warnings: 0

通过正则批量替换将 \n", 替换为 , 使表格数据没有问题：

mysql> LOAD DATA INFILE '/var/lib/mysql-files/profession.csv' INTO TABLE professionss FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Query OK, 2220 rows affected (0.06 sec)

Records: 2220 Deleted: 0 Skipped: 0 Warnings: 0