MySQL导入导出数据库及表数据

介于我们平时在使用MySQL导入导出数据很是频繁,我特别搜集整理了一下,希望对小伙伴们有所帮助!

一、导出表文件

查询表tb_user中的nickname,phone字段,导出到/tmp目录下的test.csv文件。

SELECT nickname,phone FROM tb_user
INTO OUTFILE '/tmp/test.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

 

FIELDS TERMINATED BY ---- 字段终止字符 
OPTIONALLY ENCLOSED BY ---- 封套符 
LINES TERMINATED BY ---- 行终止符

 

特别注意:以上语句的导出经常会出现如下的错误

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_file_priv";

+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |

解决此方法:

一些版本的mysql对通过文件导入导出作了限制,默认不允许,
查看配置,执行mysql命令
SHOW VARIABLES LIKE "secure_file_priv";
如果value值为null,则为禁止,如果有文件夹目录,则只允许改目录下文件(测试子目录也不行),如果为空,则不限制目录;
 
修改配置可修改mysql配置文件,查看是否有
secure_file_priv = 
这样一行内容,如果没有,则手动添加,
secure_file_priv = /home 
表示限制为/home文件夹
secure_file_priv = 
修改完配置文件后,重启mysql生效

正常导出结果为:

mysql> SELECT nickname,phone FROM vwc_user_wechat  INTO OUTFILE '/var/lib/mysql-files/test.csv'    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'    LINES TERMINATED BY '\r\n';
Query OK, 513 rows affected (0.01 sec)

 未完待续........

posted @ 2021-10-19 10:42  柴高八斗  阅读(641)  评论(0编辑  收藏  举报