方式一：
SELECT
id, name
FROM
artiles
WHERE
name LIKE '%名%'
OR intro LIKE '%名%'
INTO OUTFILE 'my_data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\n';
可能會(huì)出現(xiàn)的問(wèn)題：
1.secure_file_priv,查看變量得知默認(rèn)只能導(dǎo)出到/var/lib/mysql-files/目錄中
mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
該配置是指定允許導(dǎo)出到具體的某個(gè)目錄中,不能通過(guò)命令行的方式直接修改：
mysql> set secure_file_priv = '/home/';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
編輯mysql配置文件修改此項(xiàng)配置
[mysqld_safe]
#secure_file_priv：NULL：不允許導(dǎo)出, 具體路徑：只能導(dǎo)出到該路徑中，空：不限制導(dǎo)出目錄
secure_file_priv =
重啟mysql
systemctl restart mysql
2.創(chuàng)建文件權(quán)限問(wèn)題
mysql> select * from articles into outfile '/home/tmp/table.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';
ERROR 1 (HY000): Can't create/write to file '/home/tmp/table.csv' (Errcode: 13 - Permission denied)
解決方式：
1.chmod -R 777 /home/tmp
2.setfacl -R -m user:mysql:rwx /home/tmp
3.修改mysql默認(rèn)的導(dǎo)出目錄
查看當(dāng)前默認(rèn)目錄:
mysql> show variables like "%secure_file_priv%";
Snipaste_2020-01-15_13-16-52.png.png
secure_file_priv為null 表示不允許導(dǎo)入導(dǎo)出
secure_file_priv指定文件夾時(shí)，表示mysql的導(dǎo)入導(dǎo)出只能發(fā)生在指定的文件夾
secure_file_priv沒(méi)有設(shè)置時(shí)，則表示沒(méi)有任何限制
臨時(shí)修改或修改配置文件均可,但是線上不建議這么操作
方式二：
mysql -h服務(wù)器 -u用戶名 -p密碼 --default-character-set=utf8 -e "select語(yǔ)句" > /home/tmp/csv表名.csv
缺點(diǎn)就是不能指定字段間隔符和換行符,導(dǎo)出為csv文件時(shí)不太方便
建議使用第一種方式
