Mysql将查询结果导出到文件(select ... into Statement)

引言

我们会经常遇到需要将SQL查询结果导出到文件，以便后续的传输或数据分析的场景，为了满足这个需求，Mysql的select语句提供了into子句可以讲查询结果直接导出到文本文件，本文就Mysql中的select ... into的用法进行演示

SELECT … into语句简介

select ... into语句不仅仅能够将查询结果导出到csv文件，实际上它有3中用法：

• select ... into @var_list from...: 将查询结果写入到一组变量中

• select ... into outfile from ...: 将数据写入操作系统文件，可以自定义数据格式（分隔符、包裹符、转义符、换行符等）

• select ... into dunmpfile from ...: 将单一行写入文件没有任何格式化

1.1 基本语法

根据官方文挡，into子句可以出现在以下3个位置，虽然3个位置都符合语法，但select 语句中最多只能有一个into语句（3选1）

• 查询字段之后，from子句前

• 锁定字段前

• 语句最后

这里推荐into子句放在位置3，即语句的末尾，这样相对普通的select语句更为接近，更容易理解

用法示例

查询结果保存到变量

into子句的一个常用场景就是将查询结果暂时保存到变量中，以便后续查询或使用，唯二要注意的点是变量的数量要和返回列数量匹配，并且最多只能返回一行数据

1. 样例数据

   CREATE TABLE `t_order` (
     `id` int NOT NULL AUTO_INCREMENT,
     `order_no` varchar(255) DEFAULT NULL comment '订单编号',
     `user_id` varchar(255) DEFAULT NULL comment '下单用户',
     `commodity_code` varchar(255) DEFAULT NULL comment '商品编码',
     `count` int DEFAULT '0' comment '订单数量',
     `amount` double(14,2) DEFAULT '0.00' comment '订单金额',
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
   
   -- 插入数据
   INSERT INTO `t_order` (`id`, `order_no`, `user_id`, `commodity_code`, `count`, `amount`) VALUES (1, 'SP0001', '1', '40301', 100, 99.99);

2. 查询3个结果，分别保存到三个变量中

   mysql> SELECT commodity_code, user_id, amount FROM t_order LIMIT 1 into @commodity_code,@user_id,@amount;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> select @commodity_code,@user_id,@amount;
   +-----------------+----------+---------+
   | @commodity_code | @user_id | @amount |
   +-----------------+----------+---------+
   | 40301           | 1        |   99.99 |
   +-----------------+----------+---------+
   1 row in set (0.00 sec)

   这里用limit 1子句限制返回的结果只有1行，否则会报错，而且返回的是3个字段，就需要声明3个变量接收

查询结果保存到文本文件

into outfile子句可以将查询结果导出到文本文件，虽然不一定是要csv格式，但大多数情况我们都会选择这种格式。

要将Mysql中的数据写入到操作系统的文件中，首先需要具有FILE权限，需要配置安全参数secure_file_priv，不然就会出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement这错误，这个参数是限制Mysql可以写入文件的目录

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

secure_file_priv: 变量用于控制哪些目录可以用于LOAD DATA INFILE和SELCT ... INTO OUTFILE语句，如果这个变量被设置为一个特定的目录路径，那只有这个目录下，文件操作才被允许，如果没有设置或者设置为空，这些操作将被禁止，以增加服务器的安全性

修改方式有以下两种:

1. 在Mysql配置文件中设置，例如：

   [mysqld]
   secure_file_priv="/path/to/your/directory"

2. 在运行时通过管理员权限设置

   SET GLOBAL secure_file_priv = '/path/to/your/directory';

   这里是临时更改secure_file_priv的值，服务器重启失效

禁用secure_file_priv，可以通过设置为空SET GLOBAL secure_file_priv = '';来完成

在普通的select语句最后添加into outfile file_name即可将查询结果写入文件，这里的path就是secure_file_priv定义的目录

select * from t_order into outfile '/Users/xiaoyuge/Desktop/order.txt';

其实into outfile文件还隐式在文件后面附加了2个子句：

fields terminated by '\t' enclosed by '' escaped by '\\' 
lines terminated by '\n' starting by ''

• filds: 表示字段属性

• terminated by '\t'：以制表符分割字段

• enclosed by '': 不包裹字段

• escaped by '\\': 反斜杠表示转义符

• lines: 表示行属性

• terminated by '\n': 表示换行符

• starting by '' ：行的起点字符是空

如果要导出CSV格式的文件，并且以双引号”包裹字段，那么只需要增加一个fields terminated by ‘,’ enclosed by ‘“‘子句即可，其他的保持默认；

select * from t_order into outfile '/Users/xiaoyuge/Desktop/order.csv' fields terminated by ',' encolsed by '"';

查询结果保存到dumpfile

into dumpfile 子句可以将一行数据导出到文件，但是它不会做任何的分割，格式的定义，转义等操作。相对应用场景较少，这个操作通常用于将大型的BLOB字段保存到文件中，了解即可

-- 注意select语句只能返回一行结果，否则会报错。
select * from t_order limit 1 into dumpfile '/Users/xiaoyuge/Desktop/order.dump';

导出的数据没有任何分隔，都连在了一起，连换行都没