将MySQL导出为CSV，有些列带引号，有些不带引号 [英] Export MySQL to CSV, some columns with quotes and some without

问题描述

我正在导出一个MySQL表，我想导出不带双引号的整数类型的列，但要导出带双引号的varchar类型的列.我需要执行此操作，以确保下一步工作具有正确的格式.可以在MySQL中完成吗?我知道我可能可以在python脚本中执行此操作，但是csv文件很大(> 1 GB)，因此我认为可能要花一些时间.无论如何，使用MySQL查询有可能吗?

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   |     |         |       |
+-------------------------+------------------+------+-----+---------+-------+

我按照答案中的建议尝试了OPTIONALLY ENCLOSED BY '"'，但是当我将其添加到脚本中时，它会对每列加双引号，而不仅是字符串(或varchar)列.知道为什么会这样做吗?

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?

推荐答案

使用OPTIONALLY ENCLOSED BY子句.

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

OPTIONALLY修饰符使其仅对字符串列执行此操作.

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.

这篇关于将MySQL导出为CSV，有些列带引号，有些不带引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！