MYSQL入坑第一弹--------ERROR 1148 (42000)&&&ERROR 1290 (HY000)
一、运行Load data local infile命令时：
mysql> LOAD DATA LOCAL INFILE '/path/file' INTO TABLE tablename;1
出现：
ERROR 1148 (42000): The used command is not allowed with this MySQL version
这是因为：
服务器端，local_infile默认开启；客户端，local_infile默认关闭，因此用的时候需要打开。
服务器端，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 varaiables like 'local_infile';1
出现：
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)123456
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)123456
可以这么做：
mysql> set global local_infile = 'ON';1
local_infile其实是bool变量，但这里用ON。
然后可以看到：
然后可以看到：
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)1234567
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)1234567
然后再试试Load data，本人仍旧出错还是：
ERROR 1148 (42000): The used command is not allowed with this MySQL version1
因而退出mysql，执行quit或exit，然后用如下命令登录mysql：
$ mysql --local-infile=1 -u root -p1
然后就可以顺利Load data infile了。
https://dev.mysql.com/doc/refman/5.7/en/load-data-local.html
二、在做mysql 大批量导出数据的时候，所遇到的问题如下：
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql 默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作；
解决方法：
1. 查询mysql 的secure_file_priv 值配置的是什么，使用命令行： show global variables like '%secure%';
(我这里的配置是 secure_file_priv = /var/lib/mysql-files/)
2. 使用 into outfile 开始导出：(因为secure_file_priv配置的关系，所以必须导出到 /var/lib/mysql-files/目录下)
select * from table_name where a = 'test'
into outfile '/var/lib/mysql-files/test.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
into outfile '/var/lib/mysql-files/test.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
3.所用的几个关键字：
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " ' LINES TERMINATED BY '\n';
（因为导出的数据会出现一些乱码或者特殊字符，所以使用以上关键字进行转义）
参考：https://blog.csdn.net/slvher/article/details/8768468
