OUTFILE

[Asheeown]

I'm exporting results from a query into a CSV file this is what I have so far

 

 

SELECT id,name,job,address
    FROM users
    INTO OUTFILE '/tmp/users.csv'
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n' 

 

 

Now I want the first line of that csv file to be

User ID,Name,Job,Address

of course they would be in different fields since it's comma separated

[fenway]

I've never actually done this with MySQL... not sure if outfile even has such an option.

[Tyche]

You can use a UNION to achieve this (if using MySQL 4.0 or higher)

 

SELECT 'User ID','Name','Job','Address'
UNION
SELECT id,name,job,address
    FROM users
    INTO OUTFILE '/tmp/users.csv'
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n' 

 

 

[fenway]

You can use a UNION to achieve this (if using MySQL 4.0 or higher)

 

SELECT 'User ID','Name','Job','Address'
UNION
SELECT id,name,job,address
    FROM users
    INTO OUTFILE '/tmp/users.csv'
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n' 

 

 

 

Interesting... I assume this actually works? I didn't realize that it would "guess" properly.

[Tyche]

It does ... if your default character sets and collations are the same as your table - you may need to force the character /collation on the string literals (see below to force latin1/latin1_swedish_ci )

 

SELECT _latin1 'User ID' collate latin1_swedish_ci, _latin1 'Name' collate latin1_swedish_ci ....
UNION
SELECT id,Name ....