mysql 导入导出cvs文件数据到表

推荐： https://blog.csdn.net/kikajack/article/details/80529640

cvs文件导入MySql数据库命令

> mysql  -u root -p

> 输入密码

> use dbname   //切换数据库

>load data local infile '/root/1.csv' into table tablename fields terminated by ','lines terminated by '\r\n';

报错： mysql导入类型数据（内有多行数据）时出现错误 ERROR 1300 (HY000): Invalid utf8 character string: '̨

解决：

查询表中所有字段字符集，确认Collation都是想要的utf8_general_ci

mysql> show full columns from tb_model;

是utf-8 哦。

把cvs 文件用Notepad++ 打开，转码utf-8 再次导入就ok 了

 

常用参数含义：

FIELDS TERMINATED BY ','：指定字段分隔符
OPTIONALLY ENCLOSED BY '"'：认为双引号中的是一个独立的字段。Excel 转 CSV 时，有特殊字符（逗号、顿号等）的字段，会自动用双引号引起来
LINES TERMINATED BY '\n'：指定行分隔符，注意，在 Windows 平台上创建的文件，分隔符是 '\r\n'
 

当csv文件中的每一行记录的列数小于数据库表时，以下语句将十分有用（如果数据表包含自增字段，例如自增的ID， 这个语句将十分有用）：

load data local infile '/root/2.csv' into table bs_test fields terminated by ','lines terminated by '\r\n' (version_id,version_name);

 

select 导出cvs 

> SELECT id,NAME FROM bs_version into outfile '/var/lib/mysql-files/3.csv' fields terminated by ','  escaped by '"' lines terminated by '\r\n'; 

常用参数（跟 LOAD DATA INFILE 语法一样）：

FIELDS TERMINATED BY ','：指定字段分隔符
OPTIONALLY ENCLOSED BY '"'：认为双引号中的是一个独立的字段。Excel 转 CSV 时，有特殊字符（逗号、顿号等）的字段，会自动用双引号引起来
LINES TERMINATED BY '\n'：指定行分隔符，注意，在 Windows 平台上创建的文件，分隔符是 '\r\n'

select 导出cvs  常见问题

Error Code: 1290. 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                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.06 sec)

解决方案有两个：

1. 将文件复制到 secure_file_priv 指定的安全目录。推荐。
2. 在 /etc/my.cnf 配置文件（Windows 平台下是 my.ini）中关闭 secure_file_priv。这个选项无法动态配置，修改后必须重启。

 

数据中的第一个字段始终报错(我没有遇到，只是记录)

如果是整数，则报错如下：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 文件后，对于特殊字符（逗号、顿号等）的字段，会自动用双引号引起来。但是添加的双引号的位置竟然会出错：

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

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

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

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

 

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