25、导出数据

导出数据

1、使用 SELECT ... INTO OUTFILE 语句导出数据

SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt';

显示： 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决问题：

1、查看secure-file-priv的当前值：show variables like '%secure%'; 若secure-file-priv值为null 2、输入命令：select @@basedir; -----找到my.ini配置文件 3、打开配置文件，添加：secure_file_priv="/"在文件最后 4、重启就好了

SELECT * FROM test_increment INTO OUTFILE '/SQL/tmp/test_increment.txt';

2、你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

SELECT * FROM passwd INTO OUTFILE '/SQL/tmp/test_increment1.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

SELECT a,b,a+b INTO OUTFILE '/SQL/tmp/test_increment1.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_increment;