Mysql导入CSV文件遇到的各种坑

有一个csv文件需要导入到Mysql数据库，总共约600多万行，500Mb大小，用工具导入遇到各种坑。最后决定用load data 命令来导入

1.导入csv文件报错

• 输入导入命令

  load data infile 'bxltest.csv' into table ty_sale_taocan fields terminated by ',' optionally enclosed by '"' escaped by '"'lines terminated by '\r\n';

• 报错信息

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

• 查看设置参数

mysql> show variables like '%secure%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| require_secure_transport | OFF                        |
| secure_auth              | ON                         |
| secure_file_priv         | Null                       |
+--------------------------+----------------------------+

2.设置secure-file-priv选项

2.1 查看Mysql配置文件所在路径

FPMBA:etc eryuefei$ /usr/local/bin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 

2.2编辑my.conf配置文件

FPMBA:etc eryuefei$ vi /usr/local/etc/my.cnf
<!--以下是my.conf文件内容-->
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
secure_file_priv = /Users/eryuefei/Downloads
~                                                         

查看配置参数

mysql> show variables like '%secure%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| require_secure_transport | OFF                        |
| secure_auth              | ON                         |
| secure_file_priv         | /Users/eryuefei/Downloads/ |
+--------------------------+----------------------------+
3 rows in set (0.01 sec)

配置成功！

3.重新导入CSV文件

• 输入导入命令

  load data infile 'bxltest.csv' into table ty_sale_taocan fields terminated by ',' optionally enclosed by '"' escaped by '"'lines terminated by '\r\n';

• 报错信息

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

• 报错原因分析

必须要使用绝对路径，而且文件路径必须是在配置的参数底下才不会报错。

• 修改文件地址，重新导入，导入OK

mysql> load data infile '/Users/eryuefei/Downloads/bxltest.csv' into table ty_sale_taocan fields terminated by ',' optionally enclosed by '"' escaped by '"'lines terminated by '\r\n';

Query OK, 6770972 rows affected (1 min 2.39 sec)
Records: 6770972  Deleted: 0  Skipped: 0  Warnings: 0

终于导入成功，大家也看到了哦，将近680万条数据，导入总共才用了1分多钟。