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,中国重工,中国船舶重工股份有限公司