如何以CSV格式输出MySQL查询结果？

Question

有没有一种简单的方法可以从 Linux 命令行运行 MySQL 查询并以 CSV 格式输出结果 格式？

这就是我现在正在做的事情：

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

当有很多列需​​要用引号引起来时，或者结果中存在需要转义的引号时，它会变得混乱。

Answer 1

来自 将 MySQL 查询结果保存到文本或 CSV 文件中：

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

该语法可能需要重新排序

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

注意：在更新版本的 MySQL 中，

。 使用此命令，不会导出列名称。

另请注意，/var/lib/mysql-files/orders.csv 将位于运行 MySQL 的服务器上。 运行 MySQL 进程的用户必须具有写入所选目录的权限，否则命令将失败。

如果您想从远程服务器（尤其是托管或虚拟机，例如 Heroku< /a> 或 Amazon RDS），此解决方案不合适。

Answer 2

mysql your_database --password=foo < my_requests.sql > out.tsv

这会产生制表符分隔的格式。 如果您确定逗号不会出现在任何列数据中（制表符也不会出现），您可以使用此管道命令来获取真正的 CSV（感谢 致用户John Carter）：

... .sql | sed 's/\t/,/g' > out.csv

Answer 3

mysql --batch，-B

使用制表符作为列分隔符打印结果，每行都在
新队。 使用此选项，mysql 不使用历史文件。
批处理模式会导致非表格输出格式和转义
特殊字符。 使用原始模式可以禁用转义； 看
--raw 选项的描述。

这将为您提供一个制表符分隔的文件。 由于逗号（或包含逗号的字符串）不会被转义，因此将分隔符更改为逗号并不简单。

Answer 4

这是一种相当粗糙的方法^[1]：

mysql --user=wibble --password mydatabasename -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

效果很好。 不过，正则表达式再次被证明是只写的。

---

正则表达式解释：

• s/// 表示将第一个 // 之间的内容替换为第二个 // 之间的内容 //
• 末尾的“g”是修饰符，表示“所有实例，而不仅仅是第一个”
• ^（在此上下文中）表示开始of line
• $ （在这种情况下）意味着行尾

所以，把它们放在一起：

s/'/\'/          Replace ' with \'
s/\t/\",\"/g     Replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          At the end of the line, place a "
s/\n//g          Replace all \n (newline) with nothing

^{[1] 我在某个地方找到了它，不能接受任何功劳。}

Answer 5

通过 'tr' 进行管道传输（仅限 Unix/Cygwin）：

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

注意：这不会处理嵌入的逗号，也没有嵌入的选项卡。

Answer 6

这救了我几次。 它速度快而且有效！

--batch
使用 tab 作为列分隔符打印结果，每行都在
新行。

--raw < em>禁用字符转义（\n、\t、\0 和 \）

示例：

mysql -udemo_user -p -h127.0.0.1 --port=3306 \
   --default-character-set=utf8mb4 --database=demo_database \
   --batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv

为了完整起见，您可以 转换为 CSV（但是要小心，因为选项卡可能位于字段值内 - 例如文本字段）

tr '\t' ',' < 文件.tsv > 文件.csv

Answer 7

OUTFILE 解决方案 Paul Tomblin 给出< /a> 会导致在 MySQL 服务器本身上写入一个文件，因此仅当您具有 FILE 访问，以及登录访问或从该框中检索文件的其他方式。

如果您没有此类访问权限，并且制表符分隔的输出是 CSV 的合理替代品（例如，如果您的最终目标是导入到 Excel），则 serbaut 的解决方案（使用 mysql --batch 和可选的 --raw) 是正确的选择。

Answer 8

MySQL Workbench 可以将记录集导出为 CSV，而且它似乎可以很好地处理字段中的逗号出色地。 CSV 在 OpenOffice Calc 很好。

Answer 9

迄今为止的所有解决方案，除了 MySQL Workbench 一个是不正确的，并且对于 MySQL 数据库中的至少某些可能的内容来说很可能是不安全的（即安全问题）。

MySQL Workbench（以及类似的 phpMyAdmin）提供了形式上正确的解决方案，但它们是为下载输出而设计的到用户的位置。 它们对于自动化数据导出之类的事情不太有用。

无法从 mysql -B -e 'SELECT ...' 的输出生成可靠、正确的 CSV 内容，因为它无法对字段中的回车符和空格进行编码。 mysql 的“-s”标志确实会进行反斜杠转义，并且可能会导致正确的解决方案。 然而，使用脚本语言（一种具有良好内部数据结构的语言，而不是 Bash）以及已经仔细解决了编码问题的库要安全得多。

我想过为此编写一个脚本，但当我想到我要给它起什么名字时，我就想到要搜索现有的同名作​​品。 虽然我还没有彻底了解它，但 mysql2csv 看起来很有希望。 根据您的应用程序，指定 SQL 命令的 YAML 方法可能会也可能不会有吸引力。 我对比我的 Ubuntu 12.04 (Precise Pangolin) 笔记本电脑或 Debian 6.0 (Squeeze) 服务器。 是的，我知道我可以使用 RVM，但我不想为了如此简单的目的而维护它。

Answer 10

此页面上的许多答案都很薄弱，因为它们不处理 CSV 格式中可能发生的一般情况。 例如，字段中嵌入的逗号和引号以及最终总会出现的其他条件。 我们需要一个适用于所有有效 CSV 输入数据的通用解决方案。

这是一个简单而强大的 Python 解决方案：

#!/usr/bin/env python

import csv
import sys

tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab)
comma_out = csv.writer(sys.stdout, dialect=csv.excel)

for row in tab_in:
    comma_out.writerow(row)

将该文件命名为 tab2csv，将其放在您的路径中，授予其执行权限，然后像这样使用它：

mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv > outfile.csv

Python CSV 处理函数涵盖了 CSV 输入的极端情况格式。

这可以改进以通过流方法处理非常大的文件。

Answer 11

使用：

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv

Answer 12

从命令行，您可以执行以下操作：

mysql -h *hostname* -P *port number* --database=*database_name* -u *username* -p -e *your SQL query* | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > *output_file_name.csv*

鸣谢： 将表从 Amazon RDS 导出到 CSV 文件

Answer 13

我遇到了同样的问题，并且 Paul's Answer 不是一个选项，因为它是 Amazon RDS。 用逗号替换制表符不起作用，因为数据嵌入了逗号和制表符。 我发现 mycli 是 mysql 客户端的替代方案，支持 CSV 输出带有 --csv 标志的框：

mycli db_name --csv -e "select * from flowers" > flowers.csv

Answer 14

此答案使用 Python 和流行的第三方库 PyMySQL。 我添加它是因为 Python 的 csv 库功能强大，足以正确地 处理许多不同风格的 .csv 并且没有其他答案使用 Python 代码与数据库交互。

import contextlib
import csv
import datetime
import os

# https://github.com/PyMySQL/PyMySQL
import pymysql

SQL_QUERY = """
SELECT * FROM my_table WHERE my_attribute = 'my_attribute';
"""

# embedding passwords in code gets nasty when you use version control
# the environment is not much better, but this is an example
# https://stackoverflow.com/questions/12461484
SQL_USER = os.environ['SQL_USER']
SQL_PASS = os.environ['SQL_PASS']

connection = pymysql.connect(host='localhost',
                             user=SQL_USER,
                             password=SQL_PASS,
                             db='dbname')

with contextlib.closing(connection):
    with connection.cursor() as cursor:
        cursor.execute(SQL_QUERY)
        # Hope you have enough memory :)
        results = cursor.fetchall()

output_file = 'my_query-{}.csv'.format(datetime.datetime.today().strftime('%Y-%m-%d'))
with open(output_file, 'w', newline='') as csvfile:
    # http://stackoverflow.com/a/17725590/2958070 about lineterminator
    csv_writer = csv.writer(csvfile, lineterminator='\n')
    csv_writer.writerows(results)

Answer 15

另外，如果您在 Bash 命令行上执行查询，我相信 tr 命令可用于将默认制表符替换为任意分隔符。

$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,

Answer 16

这很简单，它可以在任何情况下工作，不需要批处理模式或输出文件：

select concat_ws(',',
    concat('"', replace(field1, '"', '""'), '"'),
    concat('"', replace(field2, '"', '""'), '"'),
    concat('"', replace(field3, '"', '""'), '"'))

from your_table where etc;

说明：

1. 在每个字段中将 " 替换为 "" --> replace( field1, '"', '""')
2. 将每个结果用引号引起来 --> concat('"', result1, '"')
3. 在每个引用的结果之间放置一个逗号 --> concat_ws(',',quoted1,quoted2,...)

就是这样！

Answer 17

您可以拥有一个使用 CSV 引擎的 MySQL 表。

然后，您的硬盘上将有一个始终为 CSV 格式的文件，您只需复制该文件即可，无需对其进行处理。

Answer 18

为了扩展之前的答案，以下一行将单个表导出为制表符分隔的文件。 它适合自动化，每天左右导出数据库。

mysql -B -D mydatabase -e 'select * from mytable'

方便地，我们可以使用相同的技术列出 MySQL 的表，并描述单个表上的字段：

mysql -B -D mydatabase -e 'show tables'

mysql -B -D mydatabase -e 'desc users'

Field   Type    Null    Key Default Extra
id  int(11) NO  PRI NULL    auto_increment
email   varchar(128)    NO  UNI NULL    
lastName    varchar(100)    YES     NULL    
title   varchar(128)    YES UNI NULL    
userName    varchar(128)    YES UNI NULL    
firstName   varchar(100)    YES     NULL    

Answer 19

建立在 user7610 的基础上，这是最好的方法。 使用 mysql outfile 时，存在 60 分钟的文件所有权和覆盖问题。

虽然不太酷，但 5 分钟内就成功了。

php csvdump.php localhost root 密码数据库表名> 无论你喜欢什么.csv

<?php

$server = $argv[1];
$user = $argv[2];
$password = $argv[3];
$db = $argv[4];
$table = $argv[5];

mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

// fetch the data
$rows = mysql_query('SELECT * FROM ' . $table);
$rows || die(mysql_error());


// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings

$fields = [];
for($i = 0; $i < mysql_num_fields($rows); $i++) {
    $field_info = mysql_fetch_field($rows, $i);
    $fields[] = $field_info->name;
}
fputcsv($output, $fields);

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

?>

Answer 20

这就是我所做的：

echo $QUERY | \
  mysql -B  $MYSQL_OPTS | \
  perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
  mail -s 'report' person@address

Perl 脚本（从其他地方截取）很好地将制表符间隔字段转换为 CSV。

Answer 21

不完全是 CSV 格式，而是 MySQL 客户端中的 tee 命令可用于将输出保存到本地文件中：

tee foobar.txt
SELECT foo FROM bar;

您可以使用 notee< 禁用它/代码>。

SELECT … INTO OUTFILE …; 的问题是它需要在服务器上写入文件的权限。

Answer 22

就我而言， INTO OUTFILE ..... 之前的 from table_name ..... 给出错误：

子句的顺序意外。 （位置 10 处的“FROM”附近）

什么对我有用：

SELECT *
INTO OUTFILE '/Volumes/Development/sql/sql/enabled_contacts.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name
WHERE column_name = 'value'

Answer 23

对我有用的：

SELECT *
FROM students
WHERE foo = 'bar'
LIMIT 0,1200000
INTO OUTFILE './students-1200000.csv'
FIELDS TERMINATED BY ',' ESCAPED BY '"'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

该线程上的解决方案都不适合我的特定情况。 我的其中一列中有相当多的 JSON 数据，这些数据在我的 CSV 输出中会变得混乱。 对于那些有类似问题的人，请尝试以 \r\n 结尾的行。

对于那些尝试使用 Microsoft Excel 打开 CSV 的人来说，还有另一个问题，请记住，单个单元格可以容纳的字符数最多为 32,767 个字符，超过此限制，它会溢出到下面的行。 要确定列中的哪些记录存在问题，请使用以下查询。 然后，您可以截断这些记录或根据需要处理它们。

SELECT id,name,CHAR_LENGTH(json_student_description) AS 'character length'
FROM students
WHERE CHAR_LENGTH(json_student_description)>32767;

Answer 24

使用蒂姆·哈丁发布的解决方案< /a>，我创建了这个 Bash 脚本来简化该过程（需要 root 密码，但您可以轻松修改脚本以询问任何其他用户）：

#!/bin/bash

if [ "$1" == "" ];then
    echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]"
    exit
fi

DBNAME=$1
TABLE=$2
FNAME=$1.$2.csv
MCOMM=$3

echo "MySQL password: "
stty -echo
read PASS
stty echo

mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME

它将创建一个名为：database.table.csv< /强>

Answer 25

如果您在服务器上设置了 PHP，则可以使用 mysql2csv 导出一个（实际上有效的) 用于任意 MySQL 查询的 CSV 文件。 请参阅我在 MySQL - SELECT * INTO OUTFILE LOCAL 上的回答？ 了解更多上下文/信息。

我尝试维护 mysql 中的选项名称，因此提供 --file 和 --query 选项就足够了：

./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

通过“安装”mysql2csv

wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv

（下载要点内容，检查校验和并使其可执行。）

Answer 26

以下生成制表符分隔且有效的 CSV 输出。 与大多数其他答案不同，此技术可以正确处理制表符、逗号、引号和换行符的转义，而无需任何流过滤器，例如 sed、AWK 或 tr。

该示例展示了如何使用流将远程 MySQL 表直接通过管道传输到本地 SQLite 数据库。 这无需 FILE 权限或 SELECT INTO OUTFILE 权限即可工作。 为了便于阅读，我添加了新行。

mysql -B -C --raw -u 'username' --password='password' --host='hostname' 'databasename'
-e 'SELECT
    CONCAT('\''"'\'',REPLACE(`id`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''id'\'',
    CONCAT('\''"'\'',REPLACE(`value`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''value'\''
    FROM sampledata'
2>/dev/null | sqlite3 -csv -separator 
  需要 2>/dev/null 来抑制命令行上有关密码的警告。
  如果您的数据包含 NULL，您可以在查询中使用 IFNULL() 函数。
\t' mydb.db '.import /dev/stdin mycsvtable'

需要 2>/dev/null 来抑制命令行上有关密码的警告。

如果您的数据包含 NULL，您可以在查询中使用 IFNULL() 函数。

Answer 27

Python 中的一个简单解决方案，可编写带有标头的标准格式 CSV 文件并将数据作为流写入（内存使用量低）：

import csv

def export_table(connection, table_name, output_filename):
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM " + table_name)

    # thanks to https://gist.github.com/madan712/f27ac3b703a541abbcd63871a4a56636 for this hint
    header = [descriptor[0] for descriptor in cursor.description]

    with open(output_filename, 'w') as csvfile:
        csv_writer = csv.writer(csvfile, dialect='excel')
        csv_writer.writerow(header)
        for row in cursor:
            csv_writer.writerow(row)

您可以像这样使用它：

import mysql.connector as mysql
# (or https://github.com/PyMySQL/PyMySQL should work but I haven't tested it)

db = mysql.connect(
    host="localhost",
    user="USERNAME",
    db="DATABASE_NAME",
    port=9999)

for table_name in ['table1', 'table2']:
    export_table(db, table_name, table_name + '.csv')

db.close()

为简单起见，这故意不包含 另一个答案，例如使用环境变量作为凭据、contextlib等。那里提到了关于行结尾的微妙之处我还没有评价过。

Answer 28

用于对 CSV 转储进行简单查询的小型 Bash 脚本，灵感来自 蒂姆·哈丁的回答。

#!/bin/bash

# $1 = query to execute
# $2 = outfile
# $3 = mysql database name
# $4 = mysql username

if [ -z "$1" ]; then
    echo "Query not given"
    exit 1
fi

if [ -z "$2" ]; then
    echo "Outfile not given"
    exit 1
fi

MYSQL_DB=""
MYSQL_USER="root"

if [ ! -z "$3" ]; then
    MYSQL_DB=$3
fi

if [ ! -z "$4" ]; then
    MYSQL_USER=$4
fi

if [ -z "$MYSQL_DB" ]; then
    echo "Database name not given"
    exit 1
fi

if [ -z "$MYSQL_USER" ]; then
    echo "Database user not given"
    exit 1
fi

mysql -u $MYSQL_USER -p -D $MYSQL_DB -B -s -e "$1" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $2
echo "Written to $2"

Answer 29

如果您收到 secure-file-priv 错误，那么在将目标文件位置移动到 C:\ProgramData\MySQL\MySQL Server 8.0\Uploads 后，然后查询 -

SELECT * FROM attend INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n ';

不起作用，您只需将查询中的 \(backsplash) 更改为 / (forwardsplash)

即可，

示例：

从出勤中选择 * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/FileName.csv' 字段以 ',' 结尾，由 '"' 结尾，行以 '\n' 结尾；

每次运行成功时查询，每次都会生成新的CSV文件！
很酷，对吧？

Answer 30

这又脏又丑。 它仅适用于您拥有的只是 PHP-*-admin 并且服务器使用 --secure-file-priv 选项运行的特定情况，因此您不能使用 INTO OUTFILE '/path/to/export.csv' 查询中的子句。

您可以做的是使用...等待它！，CONCAT 解析 CSV 行，然后复制结果并将其粘贴到文件中。

这是一个示例，我需要 SQL 格式（将其调整为 CSV 很简单）：

SELECT CONCAT(
"('",
`username`, "', '",
`password`, "', '",
`first_name`, "', '",
`last_name`, "', '",
`gender`, "'),"
) AS `row` 
FROM `users`
WHERE `role` != 'Not set'
AND `user_status` = 'Active'
ORDER BY `role`, `gender`, `user_id`
LIMIT 200

这提供了很好的、准备好导入的输出，类似于：

('jane', '3d7ff...', 'Jane', 'Doe', 'Female'),  
('john', 'd2a33...', 'John', 'Doe', 'Male'),
...