csv导入mysql_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-pr