mysql导入csv文件_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

# 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分多钟。