MySQL数据导入的常用方式

环境：MySQL 5.7

LOAD DATA INFILE

MySQL的LOAD DATA INFILE可以快速导入各种格式化的数据。现有位置数据需要导入：

数据为一般文本，位于/root/d_dt_region，以","分割列，以"包裹字段。执行mysql -uroot -p进入MySQL Shell，使用以下命令：

LOAD DATA INFILE "/root/t_dt_region.csv" 
INTO TABLE t_dt_region 
FIELDS TERMINATED BY "," ENCLOSED BY "\"" 
LINES TERMINATED BY "\r\n" 
(ID,PARENT_ID,NAME,SHORT_NAME,LONGITUDE,LATITUDE,LEVEL,SORT,STATUS);

异常处理

secure-file-priv设置

报错如下：

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

报错原因：MySQL文件的导入和导出路径有默认的设置，即secure-file-priv，当传入的csv文件路径与默认的路径冲突时就会报错。

secure-file-priv的值可以在/etc/my.cnf中配置，有三种情况：

secure_file_priv=null ––限制mysqld不允许导入导出
secure_file_priv=/path/ --限制mysqld的导入导出只能发生在默认的/path/目录下
secure_file_priv='' --不对mysqld的导入导出做限制

可以使用SHOW VARIABLES LIKE "%secure%"查看当前的secure-file-priv，

+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+

可以看到默认目录为/var/lib/mysql-files/。可以将csv文件移动到该目录下，也可修改/etc/my.cnf配置：

[mysqld]
secure-file-priv=''

修改后重启MySQL，再次查看SHOW VARIABLES LIKE "%secure%"，看到secure_file_priv字段修改为空，此时已经可以从其他目录导入数据了。

max_allowed_packet设置

报错如下：

Packet for query is too large (1139736> 1048576). You can change this value on the server by setting

如果csv文件过大，还需要调大/etc/my.cnf中的max_allowed_packet字段：

[mysqld]
max_allowed_packet=256M

行分隔符错误

报错如下：

Data truncated for column 'xxx' at row 1

检查LINES TERMINATED BY "\r\n"语句，如果写成LINES TERMINATED BY "\n"可能会出现这种问题