Export MySQL to CSV, some columns with quotes and some without

Question

I am exporting a MySQL table and I want to export the integer type columns without double quotes but the varchar type columns with double quotes. I need to do this to have the correct formatting for the next step in my work. Can this be done in MySQL? I know I could probably do this in a python script but the csv files are pretty large (>1 GB) so I think it might take awhile to do that. Anyway, is this possible using MySQL Queries?

Here's my current export script format:

SELECT 
   'column_name_1',
   'column_name_2',
   ...
   'column_name_n'
UNION ALL
SELECT *
FROM table
INTO OUTFILE 'table.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

If it helps, here is the table (more importantly, the types involved) I am trying to export:

+-------------------------+------------------+------+-----+---------+-------+
| Field                   | Type             | Null | Key | Default | Extra |
+-------------------------+------------------+------+-----+---------+-------+
| field_1                 | int(10) unsigned | NO   | MUL | 0       |       |
| field_2                 | int(10) unsigned | NO   | MUL | NULL    |       |
| field_3                 | int(10) unsigned | NO   |     | NULL    |       |
| field_4                 | char(1)          | NO   |     | NULL    |       |
| field_5                 | int(10) unsigned | NO   |     | NULL    |       |
| field_6                 | varchar(4)       | NO   |     |         |       |
| field_7                 | char(1)          | NO   |     | Y       |       |
| field_8                 | varchar(20)      | NO   |     |         |       |
| field_9                 | varchar(200)     | NO   |     |         |       |
+-------------------------+------------------+------+-----+---------+-------+

EDIT 1: I tried OPTIONALLY ENCLOSED BY '"' as suggested in an answer, but when I add that to the script, it double quotes every column, not just the string (or varchar) columns. Any idea why it might do this?

Solution

use the OPTIONALLY ENCLOSED BY clause.

SELECT *
FROM table
INTO OUTFILE 'table.csv'
FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The OPTIONALLY modifier makes it do this only for string columns.

You also need to leave out the subquery that returns the header line. The problem is that all rows of a union need to have the same types in the columns, so it's converting all the non-strings to strings to match the header line.

Comments

[Sergei Wallace]

When I use OPTIONALLY ENCLOSED BY '"', it encloses every column with quotes?

[Barmar]

I just tried it myself and the integer columns didn't get quoted. Maybe it's dependent on sql modes or some other setting?

[Barmar]

What version of MySQL? I'm using 5.5.43.

[Barmar]

I see the problem. The first SELECT that returns the column names causes all the columns to be treated as strings.

[Barmar]

It's converting the results of the second subquery into strings, because all the rows of a UNION have to have the same datatype.

[Sergei Wallace]

I see, is there a way to still select the column names but use optionally enclosed by as well?

[Barmar]

I don't think there is. You'll need to insert the header line into the file as a separate step outside MySQL.

[Sergei Wallace]

Alright. That's okay at this point since I already have other files with the headers I can copy. Thanks for the help.