将 MySQL 命令行结果的输出格式更改为 CSV

Change output format for MySQL command line results to CSV

提问by spiffytech

I want to get headerless CSV data from the output of a query to MySQL on the command line. I'm running this query on a different machine from the MySQL server, so all those Google answers with "INTO OUTFILE" are no good.

我想在命令行上从对 MySQL 的查询输出中获取无头 CSV 数据。我在与 MySQL 服务器不同的机器上运行这个查询，所以所有那些带有“INTO OUTFILE”的谷歌答案都不好。

So I run mysql -e "select people, places from things". That outputs stuff that looks kinda like this:

所以我跑mysql -e "select people, places from things"。输出看起来有点像这样的东西：

+--------+-------------+
| people | places      |
+--------+-------------+
|   Bill | Raleigh, NC |
+--------+-------------+

Well, that's no good. But hey, look! If I just pipe it to anything, it turns it into a tab-separated list:

嗯，那不好。但是，嘿，看！如果我只是将其通过管道传输到任何内容，它会将其转换为制表符分隔的列表：

people  places
Bill    Raleigh, NC

That's better- at least it's programmatically parseable. But I don't want TSV, I want CSV, and I don't want that header. I can get rid of the header with mysql <stuff> | tail -n +2, but that's a bother I'd like to avoid if MySQL just has a flag to omit it. And I can't just replace all tabs with commas, because that doesn't handle content with commas in it.

那更好 - 至少它可以通过编程方式解析。但我不想要 TSV，我想要 CSV，而且我不想要那个标题。我可以用 去掉标头mysql <stuff> | tail -n +2，但如果 MySQL 只是有一个标志来省略它，我想避免这种麻烦。而且我不能只用逗号替换所有选项卡，因为这不能处理其中包含逗号的内容。

So, how can I get MySQL to omit the header and give me data in CSV format?

那么，如何让 MySQL 省略标题并以 CSV 格式提供数据？

采纳答案by spiffytech

I wound up writing my own command-linetool to take care of this. It's similar to cut, except it knows what to do with quoted fields, etc. This tool, paired with @Jimothy's answer, allows me to get a headerless CSV from a remote MySQL server I have no filesystem access to onto my local machine with this command:

我最终编写了自己的命令行工具来处理这个问题。它类似于cut，除了它知道如何处理带引号的字段等。这个工具与@Jimothy 的答案配对，允许我从远程 MySQL 服务器获取无头 CSV 我没有文件系统访问权限到我的本地机器上使用此命令：

$ mysql -N -e "select people, places from things" | csvm -i '\t' -o ','
Bill,"Raleigh, NC"

csvmaster on github

github上的csvmaster

回答by Jimothy

As a partial answer: mysql -N -B -e "select people, places from things"

作为部分答案： mysql -N -B -e "select people, places from things"

-Ntells it not to print column headers. -Bis "batch mode", and uses tabs to separate fields.

-N告诉它不要打印列标题。-B是“批处理模式”，并使用制表符分隔字段。

If tab separated values won't suffice, see this Stackoverflow Q&A.

如果制表符分隔值不够，请参阅此 Stackoverflow Q&A。

回答by Chris Johnson

The above solutions only work in special cases. You'll get yourself into all kinds of trouble with embedded commas, embedded quotes, other things that make CSV hard in the general case.

上述解决方案仅适用于特殊情况。在一般情况下，您会因嵌入逗号、嵌入引号以及其他使 CSV 变得困难的东西而陷入各种麻烦。

Do yourself a favor and use a general solution - do it right and you'll never have to think about it again. One very strong solution is the csvkitcommand line utilities - available for all operating systems via Python. Install via pip install csvkit. This will give you correct CSV data:

帮自己一个忙，使用通用的解决方案 - 做对了，你就不必再考虑了。一个非常强大的解决方案是csvkit命令行实用程序 - 可通过 Python 用于所有操作系统。通过pip install csvkit. 这将为您提供正确的 CSV 数据：

    mysql -e "select people, places from things" | csvcut -t

That produces comma-separated data with the header still in place. To drop the header row:

这会产生以逗号分隔的数据，并且标头仍然存在。要删除标题行：

    mysql -e "select people, places from things" | csvcut -t | tail -n +2

That produces what the OP requested.

这产生了 OP 要求的内容。

回答by Artur Siara

It is how to save results to CSV on the client-side without additional non-standard tools. This example uses onlymysqlclient and awk.

它是如何在客户端将结果保存到 CSV 而无需额外的非标准工具。此示例仅使用mysql客户端和awk.

One-line:

一条线：

mysql --skip-column-names --batch -e 'select * from dump3' t | awk -F'\t' '{ sep=""; for(i = 1; i <= NF; i++) { gsub(/\t/,"\t",$i); gsub(/\n/,"\n",$i); gsub(/\\/,"\",$i); gsub(/"/,"\"\"",$i); printf sep"\""$i"\""; sep=","; if(i==NF){printf"\n"}}}'

Logical explanation of what is needed to do

需要做什么的逻辑解释

1. First, let see how data looks like in RAW mode(with --rawoption). the database and table are respectively tand dump3

   You can see the field starting from "new line" (in the first row) is splitted into three lines due to new lines placed in the value.

1. 首先，让我们看看数据在 RAW 模式下的样子（带--raw选项）。数据库和表分别是t和dump3

   由于在值中放置了新行，您可以看到从“新行”（第一行）开始的字段被分成三行。

mysql --skip-column-names --batch --raw -e 'select * from dump3' t

one line        2       new line
quotation marks " backslash \ two quotation marks "" two backslashes \ two tabs                new line
the end of field

another line    1       another line description without any special chars

1. OUTPUT data in batch mode(without --rawoption) - each record changed to the one-line texts by escaping characters like \<tab>and new-lines

2. 批处理模式下的 OUTPUT 数据（无--raw选项） - 每条记录通过转义字符更改为一行文本，如\<tab>和new-lines

mysql --skip-column-names --batch -e 'select * from dump3' t

one line      2  new line\nquotation marks " backslash \ two quotation marks "" two backslashes \\ two tabs\t\tnew line\nthe end of field
another line  1  another line description without any special chars

1. And data output in CSV format

3. 并以CSV格式输出数据

The clue is to save data in CSV format with escaped characters.

线索是将数据保存为带有转义字符的 CSV 格式。

The way to do that is to convert special entities which mysql --batchproduces (\tas tabs \\as backshlash and \nas newline) into equivalent bytes for each value (field). Then whole value is escaped by "and enclosed also by ". Btw - using the same characters for escaping and enclosing gently simplifies output and processing, because you don't have two special characters. For this reason all you have to do with values (from csv format perspective) is to change "to ""whithin values. In more common way (with escaping and enclosing respectively \and ") you would have to first change \to \\and then change "into \".

做到这一点的方法是将转换哪个特殊实体mysql --batch产生（\t如凸片\\作为backshlash和\n作为新行）成每个值（字段）等效字节。然后整个值被转义并被"包围"。顺便说一句 - 使用相同的字符进行转义和封闭可以轻轻地简化输出和处理，因为您没有两个特殊字符。出于这个原因，您对值所做的一切（从 csv 格式的角度来看）就是更改"为""whithin 值。以更常见的方式（分别转义和封闭\和"），您必须先更改\为\\，然后更改"为\"。

And the commands' explanation step by step:

以及命令的逐步解释：

# we produce one-line output as showed in step 2.
mysql --skip-column-names --batch -e 'select * from dump3' t

# set fields separator to  because mysql produces in that way
| awk -F'\t' 

# this start iterating every line/record from the mysql data - standard behaviour of awk
'{ 

# field separator is empty because we don't print a separator before the first output field
sep=""; 

-- iterating by every field and converting the field to csv proper value
for(i = 1; i <= NF; i++) { 
-- note: \ two shlashes below mean \ for awk because they're escaped

-- changing \t into byte corresponding to <tab> 
    gsub(/\t/, "\t",$i); 

-- changing \n into byte corresponding to new line
    gsub(/\n/, "\n",$i); 

-- changing two \ into one \  
    gsub(/\\/,"\",$i);

-- changing value into CSV proper one literally - change " into ""
    gsub(/"/,   "\"\"",$i); 

-- print output field enclosed by " and adding separator before
    printf sep"\""$i"\"";  

-- separator is set after first field is processed - because earlier we don't need it
    sep=","; 

-- adding new line after the last field processed - so this indicates csv record separator
    if(i==NF) {printf"\n"} 
    }
}'

回答by xdaxdb

How about using sed? It comes standard with most (all?) Linux OS.

使用sed怎么样？它是大多数（全部？）Linux 操作系统的标准配置。

sed 's/\t/<your_field_delimiter>/g'.

sed 's/\t/<your_field_delimiter>/g'.

This example uses GNU sed (Linux). For POSIX sed (AIX/Solaris)I believe you would type a literal TAB instead of \t

本示例使用 GNU sed (Linux)。对于 POSIX sed (AIX/Solaris)，我相信您会输入文字 TAB 而不是\t

Example (for CSV output):

示例（用于 CSV 输出）：

#mysql mysql -B -e "select * from user" | while read; do sed 's/\t/,/g'; done

localhost,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
localhost,bill,*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,,
127.0.0.1,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
::1,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
%,jim,*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,,

回答by b.b3rn4rd

mysqldumputility can help you, basically with --taboption it's a wrapped for SELECT INTO OUTFILEstatement.

mysqldump实用程序可以帮助你，基本上--tab它是一个包装的 forSELECT INTO OUTFILE语句。

Example:

例子：

mysqldump -u root -p --tab=/tmp world Country --fields-enclosed-by='"' --fields-terminated-by="," --lines-terminated-by="\n" --no-create-info

This will create csv formatted file /tmp/Country.txt

这将创建 csv 格式的文件 /tmp/Country.txt