mysql csv数据和头导出

cx6n0qe3 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(324)

我也尝试过从mysql数据库中导出csv文件。但我得到以下错误-

ERROR 1222 (21000): The used SELECT statements have a different number of columns

mysql查询如下-

SELECT * FROM (
  (SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'ORDERS'
    AND TABLE_SCHEMA = 'REMOTE'
    ORDER BY ORDINAL_POSITION)
  UNION ALL
  (SELECT * FROM ORDERS WHERE orderDate>='2018-05-07')) AS TBL
INTO OUTFILE 'C:/xampp/htdocs/WES/DBScript/SS_Orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

mysql mariadb

来源：https://stackoverflow.com/questions/50259631/mysql-csv-export-with-data-and-header

1条答案

按热度按时间

3okqufwl1#

通常你必须这样做：

SELECT *
FROM (
    select 'A', 'B', 'C'
    union all
    select a, b, c from table
    ) t
INTO OUTFILE [...]

请注意，您必须确保所有列数据类型都正确匹配，这意味着它们都需要是varchar或类似的类型。
对于头文件，您可以通过以下方式实现自动化：

SELECT * FROM (
SELECT MAX(CASE WHEN ORDINAL_POSITION = 1 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 2 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 3 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 4 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 5 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 6 THEN COLUMN_NAME END),
    [...]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ORDERS'
    AND TABLE_SCHEMA = 'REMOTE'
UNION ALL
SELECT [...]
) t
INTO OUTFILE [...]

但你还是要知道列的数目。如果您想动态地执行它，那么基本上需要动态sql。
仅仅编写一个python脚本来实现它可能更容易。

赞(0）回复(0）举报 2021-06-25

我来回答

mysql csv数据和头导出

1条答案

相关问题

热门标签

最新问答