SELECT INTO OUTFILE only giving column names in csv

Question

I'm trying to run a query that will get me a csv with headers, I have that query running with no issues as:

SELECT * FROM (
    SELECT 'col1','col2','col3','col4','col5'
    UNION ALL
    (
        SELECT *
FROM tt1
WHERE (col1 = '12345')
AND
col2 BETWEEN 
CAST('2018-01-01' AS DATE) AND 
CAST('2021-03-31' AS DATE)
    )
) resulting_set
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Exports/changeme.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

And this outputs a csv as desired, but my table has 63 columns so I want to limit what columns are exported. I thought that

SELECT * FROM (
    SELECT 'col1','col2'
    UNION ALL
    (
        SELECT 'col1','col2'
FROM tt1
WHERE (col1 = '12345')
AND
col2 BETWEEN 
CAST('2018-01-01' AS DATE) AND 
CAST('2021-03-31' AS DATE)
    )
) resulting_set
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Exports/changeme.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Would get me what I want but, the csv I get doesn't have data but just the header name repeated in the cells like below.

col1	col2
col1	col2
col1	col2
col1	col2

score 0 · Accepted Answer · answered Aug 13 '21 at 21:16

that is because your second query has single quotes around the column names and so mysql treats that as string

'Please read before you go on this thread When to use single quotes, double quotes, and backticks in MySQL

SELECT * FROM (
    SELECT 'col1','col2'
    UNION ALL
    (
        SELECT `col1`,`col2`
FROM tt1
WHERE (col1 = '12345')
AND
col2 BETWEEN 
CAST('2018-01-01' AS DATE) AND 
CAST('2021-03-31' AS DATE)
    )
) resulting_set
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Exports/changeme.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT INTO OUTFILE only giving column names in csv

1 Answers1