MySQL:动态导出带标题的 CSV 文件

Question

有没有办法导出带有列标题的 MySQL 表中的数据？

我找到了一种通过将 header 硬编码到查询中来实现此目的的方法，但如果表中有大约 60 甚至 100 列，那么这是不可能的。

我尝试了下面的查询，但无法获得结果，因为查询的第一部分返回了表中所有标题的串联字符串。它没有给我想要的结果:

(select concat(group_concat(COLUMN_NAME separator ','), "\n")
    from information_schema.COLUMNS
    where table_name = '<table name>'
    and table_schema = '<DB name>'
    order by ORDINAL_POSITION) 
union all
(select * from <table name> into outfile "E:\\test.csv" fields terminated by "," lines terminated by "\n");

Answer 1

在mysql中获取表的字段名的方法很少，但下面的方法最适合outfile。

csv 文件的文件名是根据日期戳动态创建的，并为此使用了准备好的语句。

-- ensure mysql user has write permission on below location
SET @effectiveFileName = CONCAT('/home/myhome/temp-dev/', 'mytable','_', DATE_FORMAT(NOW(), '%Y-%m-%d'), '.csv');
-- group concat default is 1024, to avoid field names getting truncated we increase this value
SET SESSION group_concat_max_len = 10000;

SET @queryStr = (
    SELECT
    CONCAT('SELECT * INTO OUTFILE \'',
        @effectiveFileName,
        '\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' FROM (SELECT ',
        GROUP_CONCAT(CONCAT('\'', COLUMN_NAME, '\'')),
        'UNION ALL SELECT * FROM myschema.mytable WHERE myschema.mytable.myfield <=\'',
        CURDATE(),
        '\') as tmp')
        FROM
            INFORMATION_SCHEMA.COLUMNS
        WHERE
            TABLE_NAME = 'mytable' AND
            TABLE_SCHEMA = 'myschema'
        ORDER BY ORDINAL_POSITION
    );
PREPARE stmt FROM @queryStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

下面是带有静态文件名的上述查询的简单版本。

-- group concat default is 1024, to avoid field names getting truncated we increase this value
SET SESSION group_concat_max_len = 10000;
SELECT 
CONCAT('SELECT * INTO OUTFILE \'/home/myuser/myfile.csv\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'\' LINES TERMINATED BY \'\n\' FROM (SELECT ',
        GROUP_CONCAT(CONCAT('\'', COLUMN_NAME, '\'')),
        ' UNION select * from YOUR_TABLE) as tmp')
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'YOUR_TABLE'
    AND TABLE_SCHEMA = 'YOUR_SCHEMA'
ORDER BY ORDINAL_POSITION;