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?

Vonnie

“it double quotes every column, not just the string (or varchar) columns. Any idea why it might do this?” – because why not? It is perfectly ok to just quote all field values, no matter what they contain, and have a “valid” CSV file in the end. Why do you care if there’s quotes around integer values? – Jacobin

@CBroe I need to do this to have the correct formatting for the next step in my work. – Instigation

@CBroe Obviously he's not using a general-purpose CSV parser for the next step – Instigation

Right, the software that I'm importing these csv files into won't recognize the columns as integers if they have double quotes. – Vonnie

I need this because my output is going to be a bash script. – Verrucose

Answer 1

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.

Instigation

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

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

What version of MySQL? I'm using 5.5.43. – Instigation

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

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

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

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

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