MySQL | CSV数据导入MySQL各种问题解决

报错1

错误描述

Loading local data is disabled; this must be enabled on both the client and server sides禁用加载本地数据，必须在客户端和服务器端都启用
判断是参数设置问题，经查，local infile默认禁用。

load data local infile 'C:\Users\85171\Desktop\如何分析用户留存.csv' into table user_behavior_test fields terminated by ',';
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

解决办法

1、set global local_infile = 1;设置local file 启用

mysql> set global local_infile = 1;
Query OK, 0 rows affected (0.00 sec)

2、添加/修改 my.cnf配置
第二种登录时不用再加–local-infile=1，对于经常load data local infile的一劳永逸

[mysqld]
# 设置mysql服务端local-infile打开，允许load data local infile
local_infile=1

[mysql]
# 设置mysql客户端local-infile打开，允许load data local infile
local_infile=1

报错2

错误描述

LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. 由于访问限制，本地导入文件请求被拒绝。
还是设置问题，拒绝了访问

mysql> load data local infile 'C:\Users\85171\Desktop\如何分析用户留存.csv' into table user_behavior_test fields terminated by ',';
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

解决办法

登录时带上 --local-infile=1

## 此处--非注释哦
C:\WINDOWS\system32>mysql --local-infile=1 -P3307 -uroot -p 
Enter password: 

报错3

错误描述

至此终于开始提示文件或路径的问题，根据报错信息，路径中的\不见了，因此是特殊字符转译的问题

mysql> load data local infile 'C:\Users\85171\Desktop\如何分析用户留存.csv' into table user_behavior_test fields terminated by ',';
ERROR 2 (HY000): File 'C:Users85171Desktop如何分析用户留存.csv' not found (OS errno 2 - No such file or directory)

## 路径其实已无问题,但是依旧报错
mysql> load data local infile 'C:\\Users\\85171\\Desktop\\如何分析留存率.csv' into table user_behavior_test fields terminated by ',';
ERROR 2 (HY000): File 'C:\Users\85171\Desktop\如何分析留存率.csv' not found (OS errno 2 - No such file or directory)

解决办法

路径问题注意转译，全部保持英文字符

mysql> load data local infile 'C:\\Users\\85171\\Desktop\\user_behavior.csv' into table user_behavior_test fields terminated by ',';
Query OK, 91 rows affected, 3 warnings (0.01 sec)
Records: 91  Deleted: 0  Skipped: 0  Warnings: 3   ## 此处有3个告警

mysql> select * from user_behavior_test limit 10;
+-------------+--------------+---------+-----------+------------+
| uid         | appname      | usetime | usenumber | logindate  |
+-------------+--------------+---------+-----------+------------+
| 用户id     | 应用名称     |       0 |         0 | 0000-00-00 |
| 01          | 相机         |       1 |         2 | 2020-05-01 |
| 02          | 微信         |       2 |         3 | 2020-05-02 |
| 03          | 大众点评     |       4 |         2 | 2020-05-03 |
| 04          | 微信         |       6 |         3 | 2020-05-01 |
| 05          | 相机         |       3 |         1 | 2020-05-03 |
| 06          | 相机         |       2 |         3 | 2020-05-01 |
| 07          | 相机         |       2 |         2 | 2020-05-02 |
| 08          | 微信         |       1 |         1 | 2020-05-01 |
| 09          | 大众点评     |       3 |         2 | 2020-05-02 |
+-------------+--------------+---------+-----------+------------+
10 rows in set (0.00 sec)

mysql> delete from user_behavior_test where appname='应用名称';
Query OK, 1 row affected (0.01 sec)

mysql> show warnings;
Empty set (0.00 sec)

其实已经插入成功了，但是有3个告警信息，可能没有及时show，看不到了，姑且猜测是因为表头3个字段的数据类型不匹配。

报错4

问题描述

brand_id 列为INT类型，前5行数据为1,2,3,4,5、
反复检查了brands.csv文件没有问题

mysql> LOAD DATA LOCAL INFILE 'C:\\Users\\85171\\Desktop\\brands.csv' INTO TABLE brands FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Query OK, 236 rows affected (0.01 sec)
Records: 236  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from brands limit 5;
+----------+------------+
| brand_id | brand_name |
+----------+------------+
       | 1 | abc
         | | a
         | | b
         | | c
        |5 | d
+----------+------------+
5 rows in set (0.00 sec)

解决办法

指定LINES TERMINATED BY ‘\r\n’
不指定或指定为\n就会出现如上报错显示，说明LINES TERMINATED BY '\n’为默认

mysql> LOAD DATA LOCAL INFILE 'C:\\Users\\85171\\Desktop\\brands.csv' INTO TABLE brands FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Query OK, 236 rows affected (0.01 sec)
Records: 236  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from brands limit 5;
+----------+------------+
| brand_id | brand_name |
+----------+------------+
|        1 | abc        |
|        2 | a          |
|        3 | b          |
|        4 | c          |
|        5 | d          |
+----------+------------+
5 rows in set (0.00 sec)