windows下MySQL8中LOAD DATA INFILE出现The used command is not allowed with this MySQL version问题

环境：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.13    |
+-----------+

运行Load data local infile命令时：

mysql> LOAD DATA LOCAL INFILE 'D:/XXX.txt' INTO TABLE tablename;

出现：

ERROR 1148 (42000): The used command is not allowed with this MySQL version

这是因为：
服务器端，local_infile默认开启；客户端，local_infile默认关闭，因此用的时候需要打开。

On the server side: The local_infile system variable controls
server-side LOCAL capability. Depending on the local_infile setting,
the server refuses or permits local data loading by clients that have
LOCAL enabled on the client side. By default, local_infile is enabled.
On the client side: For the mysql client, local data loading is
disabled by default. To disable or enable it explicitly, use the
–local-infile=0 or –local-infile[=1] option. ‘

该怎么办呢？ 可以：

mysql> show global variables like 'local_infile';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF    |
+---------------+-------+
1 row in set (0.00 sec)

可以这么做：

mysql> set global local_infile = 'ON';

local_infile其实是bool变量，但这里用ON。
然后可以看到：

mysql> show global varaiables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

然后再试试Load data，本人仍旧出错还是：

ERROR 1148 (42000): The used command is not allowed with this MySQL version

因而退出mysql，执行quit或exit，然后用如下命令登录mysql：

$ mysql --local-infile=1 -u root -p

然后就可以顺利Load data infile了。

参考地址：https://blog.csdn.net/u013378642/article/details/81220809

错误小结：

#Issue1：
ERROR 1148 (42000): The used command is not allowed with this MySQL version

错误语句：mysql> LOAD DATA LOCAL INFILE '/var/lib/mysql/pet.txt' INTO TABLE tbl_pet;

出错原因：多了一个LOCAL关键词。当文件在客户机端时要写LOCAL，在服务器端时不用写。我的txt文件是和MySQL数据库在同一虚拟机上的，所以不用加LOCAL。

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

出错原因：MySQL设置了secure-file-priv属性对应的路径，当数据文件从其他路径导入时便会报错。

解决办法：查看secure-file-priv指定路径并把数据文件放进去。参考https://blog.csdn.net/FallingU/article/details/75675220

#Issue3：ERROR 1292 (22007): Incorrect date value: ‘’ for column ‘death’ at row 1

出错原因：一开始直接把原始表格里的数据复制到txt文件里没有处理，空值的地方是空的，不符合MySQL里空值的表示方式，所以报错。

解决办法：将空值用\N代替。参考https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

#Issue4：使用 LOAD DATA INFILE ‘/var/lib/mysql-files/pet.txt’ INTO TABLE tbl_pet; 导入数据仍然失败

错误提示：' for column 'death' at row 1 date value: 'N

出错原因：fields terminated by 默认值是’\t’，lines terminated by 默认值是’\n’，而Windows上的文件换行是’\r\n’，Mac OS X是’\r’。

解决办法：加上LINES TERMINATED BY ‘\r\n’，如下：

mysql> LOAD DATA INFILE '/var/lib/mysql-files/pet.txt' INTO TABLE tbl_pet LINES TERMINATED BY '\r\n';