mysql导出到del不加引号,将MySQL导出为CSV，有些列带引号，有些不带引号

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?

解决方案

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.