为了账号安全,请及时绑定邮箱和手机立即绑定

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

标签:
MySQL

有一个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分多钟。

点击查看更多内容
1人点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消