MySQL SELECTI...INTO OUTFILE将查询结果输出到外部文件语句

MySQL数据库的INTO OUTFILE语句是一个非常实用的功能，它可以将查询结果输出到外部文件中。这个函数可以让你轻松地将数据导出到CSV、Excel或其他格式的文件中，方便后续的数据分析和处理。可以和LOAD DATA语句进行导入和导出功能。

本文将详细介绍MySQL数据库INTO OUTFILE语句的语法和案例，并列举其注意事项。

一、INTO OUTFILE语句语法

INTO OUTFILE语句的基本语法如下：

SELECT * FROM <表名> 
INTO OUTFILE '<文件路径>'
[FIELDS TERMINATED BY '<字符>' [OPTIONALLY] ENCLOSED BY '<字符>' ESCAPED BY'<字符>' LINES TERMINATED BY '<字符>'];

[ ]中的内容是可选的，{}是集合，| 代表或,<>是中文解释。语法说明：

• FIELDS TERMINATED BY ',' 表示字段之间以逗号分隔。

• ESCAPED BY '?'：设置如何写入或读取问号为特殊字符，只能为单个字符，即设置为转义字符。

• ENCLOSED BY '"' 表示字段值用双引号括起来,OPTIONALLY ENCLOSED BY '"'表示字段值用双引号括起来（数值型字段无效）。

• LINES TERMINATED BY '\n' 表示每行以换行符结束。

二、INTO OUTFILE语句案例

下面是一个简单的INTO OUTFILE语句案例，将数据从products表导出到txt文件：

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('1', 'Product.A', 'Type 1');
INSERT INTO `products` VALUES ('2', 'Product?B', 'Type 2');
INSERT INTO `products` VALUES ('3', 'Product/C', 'Type 3');

原始数据

mysql> SELECT * FROM products;
+----+-----------+--------+
| id | name      | type   |
+----+-----------+--------+
|  1 | Product.A | Type 1 |
|  2 | Product?B | Type 2 |
|  3 | Product/C | Type 3 |
+----+-----------+--------+

导出，受限制报错

mysql> SELECT * FROM products INTO OUTFILE 'e:/data/test.txt';
ERROR 1290 (HY000): The MySQL server is running with the 
--secure-file-priv option so it cannot execute this statement

查看是否允许导入导出

查看官方文档，secure_file_priv参数用于限制LOAD DATA, into outfile, LOAD_FILE()权限。并且您必须具有权限FILE。该文件必须可供所有人读取并且其大小小于 max_allowed_packet字节(最大1073741824字节，就是1G)。

• secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

• secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。

• secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

mysql> show global VARIABLES like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+

设置secure_file_priv为空

在在my.ini中添加secure_file_priv = ''。记得要重新启动mysql。

secure_file_priv = ''

mysql> show global VARIABLES like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+

设置成功，可以执行into outfile语句了

mysql> SELECT * FROM products INTO OUTFILE 'e:/data/test.txt';
Query OK, 3 rows affected (0.01 sec)

找特殊字符

找到特殊字符"?"，用"?"转义字符

mysql> SELECT * FROM products INTO OUTFILE 'e:/data/test.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY "?" LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.00 sec)

不用OPTIONALLY案例

把数字类型的字段都包裹起来

mysql> SELECT * FROM products INTO OUTFILE 'e:/data/test.txt'
    -> FIELDS TERMINATED BY ','  ENCLOSED BY '"' ESCAPED BY "?" LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.00 sec)

三、注意事项

使用INTO OUTFILE语句时，需要注意以下几点：

• 文件路径：输出文件的路径必须是MySQL服务器可以访问的路径，通常为服务器上的本地路径。如果文件路径是网络共享路径，可能会导致导出失败。

• 文件权限：MySQL服务器需要有足够的权限来写入输出文件。如果文件所在的目录没有写入权限，导出操作将无法完成。

• 数据类型：INTO OUTFILE语句支持大部分数据类型，但有些特殊数据类型可能会在导出过程中出现问题。例如，日期和时间类型可能需要使用特定的格式化字符串来表示。

• 空值处理：如果查询结果中包含空值（NULL），INTO OUTFILE函数会将空值表示为特殊字符（如"\N"）。在导入数据时，需要将这些特殊字符视为空值进行处理。

• 大型文件：INTO OUTFILE语句会将整个查询结果一次性写入文件，对于大型数据集可能会导致性能问题。如果需要导出大型数据集，建议使用其他方法，如使用脚本将数据分批导出。

• 安全风险：使用INTO OUTFILE语句时需要注意安全风险。如果输出文件路径不正确或文件名不安全，可能会导致数据泄露或被恶意用户利用。建议在导出数据时采取适当的安全措施，如验证文件路径和文件名，限制导出文件的访问权限等。