背景
最近业务进行数据迁移，由于数据量不大，我们选择使用MySQL导入导出功能。在使用过程中遇到一些问题，这次在本文中进行总结。
问题
- 导入导出命令参数的选用。
- 报错：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
导入导出
1、创建表
创建带有json类型的表
createtable t1(id int, data json default null,dt datetime);
2、插入数据
insertinto t1(id ,dt)values(1,now());insertinto t1(id, data, dt)values(2,"[{""id"": ""1"", ""name"": ""zhang3""}, {""id"": ""2"", ""name"": ""li4""}]", now());insertinto t1(id)values(3);
查看数据结果
mysql>select*from t1;+------+-------------------------------------------------------------+---------------------+| id | data | dt |+------+-------------------------------------------------------------+---------------------+|1|NULL|2022-12-3009:48:05||2|[{"id":"1","name":"zhang3"},{"id":"2","name":"li4"}]|2022-12-3009:48:05||3|NULL|NULL|+------+-------------------------------------------------------------+---------------------+
3、导出CSV数据
报错：ERROR 1290 (HY000)
mysql>select*from t1 into outfile '/tmp/t1.csv' FIELDS TERMINATED BY',' ENCLOSED 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服务启动使用了--secure-file-priv 选项，所以不能指定任意的路径
解决办法
- 重新配置 --secure-file-priv 参数，重新启动mysql。
- 此配置，如果设置为：""，可以使用任意路径
- 若设置为NULL，禁用导入导出
- 若设置指定路径，则需要使用指定路径
- 选择指定的路径进行输出。 （推荐）
- 通过命令查看指定路径
mysql> show global variables like'secure_file_priv';+------------------+-----------------------------------------+| Variable_name | Value |+------------------+-----------------------------------------+| secure_file_priv |/var/opt/rh/rh-mysql80/lib/mysql-files/|+------------------+-----------------------------------------+
重新导出
mysql>select*from t1 into outfile '/var/opt/rh/rh-mysql80/lib/mysql-files/t1.csv' FIELDS TERMINATED BY',' ENCLOSED BY'"' LINES TERMINATED BY'\r\n';Query OK,3 rows affected (0.01 sec)
导出参数说明：
- FIELDS TERMINATED BY ',' 指定字段结束字符为：','
- ENCLOSED BY '"' 指定每个字段封闭字符为：'"'
- LINES TERMINATED BY '\r\n' 指定每行的结束字符为：'\r\n'
查看结果
sh-4.2# cat /var/opt/rh/rh-mysql80/lib/mysql-files/t1.csv"1",\N,"2022-12-30 09:48:05""2","[{\"id\": \"1\", \"name\": \"zhang3\"}, {\"id\": \"2\", \"name\": \"li4\"}]","2022-12-30 09:48:05""3",\N,\N
说明：
- \N 为NULL 转义字符
4、导入数据
创建临时表结构
createtable t1_bak(id int, data json default null,dt datetime);
导入数据：使用导出时相同的参数
mysql> load data infile '/var/opt/rh/rh-mysql80/lib/mysql-files/t1.csv'intotable t1_bak FIELDS TERMINATED BY',' ENCLOSED BY'"' LINES TERMINATED BY'\r\n';Query OK,3 rows affected (0.04 sec)Records:3 Deleted:0 Skipped:0 Warnings:0
查看结果
mysql>select*from t1_bak;+------+-------------------------------------------------------------+---------------------+| id | data | dt |+------+-------------------------------------------------------------+---------------------+|1|NULL|2022-12-3009:48:05||2|[{"id":"1","name":"zhang3"},{"id":"2","name":"li4"}]|2022-12-3009:48:05||3|NULL|NULL|+------+-------------------------------------------------------------+---------------------+3 rows inset(0.00 sec)
