简介

客户要求每天生成excel汇总单，本来考虑使用python来生成excel的，但是服务器的默认2.7版本就放弃了，直接使用mysql自带的文件导出功能来实现。

满足条件

1. 配置文件输出目录，目录默认配置在系统变量secure_file_priv，默认路径为/var/lib/mysql-files/

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

2. 修改my.ini文件，在[mysqld]增加secure_file_priv = "/path/to/your/directory"，并重启数据库。
3. MySQL的启动账户要对目录有创建文件权限，即/path/to/your/directory的owner或group应为mysql，或目录有mysql的写权限。

限制条件

1. 文件路径:

   • 出文件的路径必须是 MySQL 服务器可以访问的路径。通常，文件会被写入到服务器的文件系统中，而不是客户端。
   • 文件名不能是已存在的文件，MySQL 不允许覆盖已有文件，以防止数据丢失或篡改。

2. 权限要求:

   • 执行 SELECT ... INTO OUTFILE 需要 MySQL 用户具有 FILE 权限。
   • 服务器需要对指定目录具有写入权限。

3. 字符集:

   • 可以使用 CHARACTER SET 子句指定导出文件的字符集。如果未指定，默认使用二进制字符集，这可能导致字符集不一致的问题。

4. 字段和行分隔符:

   • 可以通过 FIELDS TERMINATED BY 和 LINES TERMINATED BY 子句自定义字段和行的分隔符。例如，可以使用逗号分隔字段，换行符分隔行。

5. 查询条件:

   • SELECT ... INTO OUTFILE 不支持在导出时直接使用 WHERE 子句来筛选数据。如果需要筛选数据，建议先将数据导入到临时表中，然后再从临时表导出。

6. 输出路径:

   • 输出路径不支持变量，所以下面示例会将SQL语句通过设定变量，字符串拼接后，通过PREPARE stmt FROM预处理将字符串定义为SQL语句，然后通过EXEC来执行语句，最后执行DEALLOCATE PREPARE释放之前准备的SQL语句，CSV文件就会生成在指定目录下了。

INTO OUTFILE语法

SELECT column1, column2, ...
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
[FIELDS TERMINATED BY 'string']
[OPTIONALLY ENCLOSED BY 'char']
[LINES TERMINATED BY 'string']
FROM table_name
WHERE your_conditions;

示例

-- 文件名: MySQL定时生成汇总清单
-- 日期：2024年12月9日
-- 时间：下午6:20:33
SET @SEARCH_DATE := date_sub(NOW(), interval 1 day);
SET @FILENAME := CONCAT('微信支付清单-', DATE_FORMAT(@SEARCH_DATE, '%Y-%m-%d'), '.csv');

SET @sql := CONCAT(
    'select col from tabname' -- 拼接业务SQL字符串
    'INTO OUTFILE "', @FILENAME, '" character set gbk ',
    'FIELDS TERMINATED BY "," ',
    'ENCLOSED BY \'\'"\'\' ',
    'LINES TERMINATED BY \'\'\\r\\n\'\' '
);

-- 准备并执行 SQL 语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;