MySQL讲义第 36 讲——select 查询之 INTO OUTFILE参数

在 SELECT 查询语句中使用 INTO OUTFILE 参数可以将查询结果保存到文本文件中。

一、SELECT … INTO OUTFILE 命令的格式

SELECT … INTO OUTFILE 命令的格式如下：

SELECT ... 
INTO OUTFILE 'file_name'
fields terminated by 'char';   

--说明：
（1）OUTFILE参数指定的文件所在的路径需要有 mysql 的访问权限，否则会报错。
（2）每一条记录的数据之间默认以 Tab 分隔，也可使用 fields terminated 参数指定分隔符。
（3）执行 Select into outfile 和 Load data infile 命令需要设置 secure_file_priv 参数。该参数的设置如下：
NULL：MySQL服务会禁止执行 Select into outfile 和 Load data infile 命令；
目录名：MySQL服务只允许在这个目录中执行文件的导入和导出操作。目录必须存在，MySQL服务不会创建它；
空字符串（''）：代表导出的文本文件可以放在任意位置。

二、查看和修改 secure_file_priv 参数的值

1、查看 secure_file_priv 参数的值

mysql> show variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.09 sec)

2、使用 SELECT … INTO OUTFILE 命令导出数据

此时，查询生成的文本文件只能放在 /var/lib/mysql-files/ 目录下：

--####  查看查询结果
mysql> SELECT
    -> company_id,
    -> company_abbreviation,
    -> company_fullname
    -> FROM
    -> company_information
    -> WHERE
    -> company_abbreviation REGEXP '石油$';
+------------+----------------------+--------------------------------------------------+
| company_id | company_abbreviation | company_fullname                                 |
+------------+----------------------+--------------------------------------------------+
| 000554     | 泰山石油             | 中国石化山东泰山石油股份有限公司                 |
| 300164     | 通源石油             | 西安通源石油科技股份有限公司                     |
| 601857     | 中国石油             | 中国石油天然气股份有限公司                       |
+------------+----------------------+--------------------------------------------------+
3 rows in set (0.00 sec)

--#####  导出数据
mysql> SELECT
    -> company_id,
    -> company_abbreviation,
    -> company_fullname
    -> FROM
    -> company_information
    -> WHERE
    -> company_abbreviation REGEXP '石油$'
    -> INTO OUTFILE
    -> '/var/lib/mysql-files/company_information_bak.txt';
Query OK, 3 rows affected (0.00 sec)

--##### 查看结果
[root@oracle-rac2 mysql-files]# cat company_information_bak.txt 
000554	泰山石油	中国石化山东泰山石油股份有限公司
300164	通源石油	西安通源石油科技股份有限公司
601857	中国石油	中国石油天然气股份有限公司

3、修改 secure_file_priv 参数的值

（1）修改配置文件 my.cnf

[root@oracle-rac2 mysql-files]# vim /etc/my.cnf

# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
### 修改 secure-file-priv 参数为空字符串，则导出的文本文件可以放到mysql能够操作的任意目录
secure-file-priv=''   
character-set-server=utf8
....

（2）重启 mysql 服务

[root@oracle-rac2 mysql-files]# systemctl restart mysqld

（3）登录 mysql，查看 secure_file_priv 参数的值

mysql> show variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

三、使用 INTO OUTFILE 参数举例

1、把文本文件导出到用户指定的目录

mysql> SELECT
    -> company_id,
    -> company_abbreviation,
    -> rtrim(company_fullname) AS company_fullname
    -> FROM
    -> company_information
    -> WHERE
    -> company_abbreviation REGEXP '^中国'
    -> INTO OUTFILE
    -> '/tmp/mysql-bak/company_information_bak01001.txt';
Query OK, 43 rows affected (0.00 sec)

查看结果：默认的字段分隔符为 tab 键。

[root@oracle-rac2 mysql-bak]# cat company_information_bak01001.txt 
000009	中国宝安	中国宝安集团股份有限公司
000035	中国天楹	中国天楹股份有限公司
000797	中国武夷	中国武夷实业股份有限公司
000951	中国重汽	中国重汽集团济南卡车股份有限公司
000996	中国中期	中国中期投资股份有限公司
002116	中国海诚	中国海诚工程科技股份有限公司
600007	中国国贸	中国国际贸易中心股份有限公司
600028	中国石化	中国石油化工股份有限公司
600050	中国联通	中国联合网络通信股份有限公司
600056	中国医药	中国医药健康产业股份有限公司
600118	中国卫星	中国东方红卫星股份有限公司
600150	中国船舶	中国船舶工业股份有限公司
600176	中国巨石	中国巨石股份有限公司
600482	中国动力	中国船舶重工集团动力股份有限公司
600536	中国软件	中国软件与技术服务股份有限公司
600730	中国高科	中国高科集团股份有限公司
600877	中国嘉陵	中国嘉陵工业股份有限公司(集团)
600977	中国电影	中国电影股份有限公司
601088	中国神华	中国神华能源股份有限公司
601106	中国一重	中国第一重型机械股份公司
601111	中国国航	中国国际航空股份有限公司
601117	中国化学	中国化学工程股份有限公司
601179	中国西电	中国西电电气股份有限公司
601186	中国铁建	中国铁建股份有限公司
601318	中国平安	中国平安保险（集团）股份有限公司
601390	中国中铁	中国中铁股份有限公司
601600	中国铝业	中国铝业股份有限公司
601601	中国太保	中国太平洋保险（集团）股份有限公司
601611	中国核建	中国核工业建设股份有限公司
601618	中国中冶	中国冶金科工股份有限公司
601628	中国人寿	中国人寿保险股份有限公司
601668	中国建筑	中国建筑股份有限公司
601669	中国电建	中国电力建设股份有限公司
601766	中国中车	中国中车股份有限公司
601800	中国交建	中国交通建设股份有限公司
601857	中国石油	中国石油天然气股份有限公司
601858	中国科传	中国科技出版传媒股份有限公司
601881	中国银河	中国银河证券股份有限公司
601888	中国国旅	中国国旅股份有限公司
601965	中国汽研	中国汽车工程研究院股份有限公司
601985	中国核电	中国核能电力股份有限公司
601988	中国银行	中国银行股份有限公司
601989	中国重工	中国船舶重工股份有限公司

2、把字段之间的分隔符修改为逗号（,）

mysql> SELECT
    -> company_id,
    -> company_abbreviation,
    -> rtrim(company_fullname) AS company_fullname
    -> FROM
    -> company_information
    -> WHERE
    -> company_abbreviation REGEXP '^中国'
    -> INTO OUTFILE
    -> '/tmp/mysql-bak/company_information_bak01002.txt'
    -> FIELDS TERMINATED BY ',';
Query OK, 43 rows affected (0.00 sec)

查看结果：

[root@oracle-rac2 mysql-bak]# cat company_information_bak01002.txt 
000009,中国宝安,中国宝安集团股份有限公司
000035,中国天楹,中国天楹股份有限公司
000797,中国武夷,中国武夷实业股份有限公司
000951,中国重汽,中国重汽集团济南卡车股份有限公司
000996,中国中期,中国中期投资股份有限公司
002116,中国海诚,中国海诚工程科技股份有限公司
600007,中国国贸,中国国际贸易中心股份有限公司
600028,中国石化,中国石油化工股份有限公司
600050,中国联通,中国联合网络通信股份有限公司
600056,中国医药,中国医药健康产业股份有限公司
600118,中国卫星,中国东方红卫星股份有限公司
600150,中国船舶,中国船舶工业股份有限公司
600176,中国巨石,中国巨石股份有限公司
600482,中国动力,中国船舶重工集团动力股份有限公司
600536,中国软件,中国软件与技术服务股份有限公司
600730,中国高科,中国高科集团股份有限公司
600877,中国嘉陵,中国嘉陵工业股份有限公司(集团)
600977,中国电影,中国电影股份有限公司
601088,中国神华,中国神华能源股份有限公司
601106,中国一重,中国第一重型机械股份公司
601111,中国国航,中国国际航空股份有限公司
601117,中国化学,中国化学工程股份有限公司
601179,中国西电,中国西电电气股份有限公司
601186,中国铁建,中国铁建股份有限公司
601318,中国平安,中国平安保险（集团）股份有限公司
601390,中国中铁,中国中铁股份有限公司
601600,中国铝业,中国铝业股份有限公司
601601,中国太保,中国太平洋保险（集团）股份有限公司
601611,中国核建,中国核工业建设股份有限公司
601618,中国中冶,中国冶金科工股份有限公司
601628,中国人寿,中国人寿保险股份有限公司
601668,中国建筑,中国建筑股份有限公司
601669,中国电建,中国电力建设股份有限公司
601766,中国中车,中国中车股份有限公司
601800,中国交建,中国交通建设股份有限公司
601857,中国石油,中国石油天然气股份有限公司
601858,中国科传,中国科技出版传媒股份有限公司
601881,中国银河,中国银河证券股份有限公司
601888,中国国旅,中国国旅股份有限公司
601965,中国汽研,中国汽车工程研究院股份有限公司
601985,中国核电,中国核能电力股份有限公司
601988,中国银行,中国银行股份有限公司
601989,中国重工,中国船舶重工股份有限公司