mysql格式化输出 - phpmyadmin导出查询结果
如何以CSV格式输出MySQL查询结果? (17)
mysql --batch，-B
使用选项卡作为列分隔符打印结果，每行放在一个新行中。 使用这个选项，mysql不会使用历史文件。 批处理模式导致非表格输出格式和转义特殊字符。 通过使用原始模式可能会禁用转义; 请参阅--raw选项的说明。
这会给你一个制表符分隔的文件。 由于逗号（或包含逗号的字符串）不会被转义，因此将分隔符更改为逗号并不简单。
有没有简单的方法来从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
当需要用引号包围很多列时，或者结果中有需要转义的引号时，它会变得杂乱无章。
- 逻辑：
CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;
当您创建CSV表时，服务器会在数据库目录中创建一个表格格式文件。 该文件以表名开头并具有.frm扩展名。 存储引擎还会创建一个数据文件。 它的名字以表名开头，扩展名为.CSV。 数据文件是纯文本文件。 将数据存储到表中时，存储引擎会以逗号分隔值格式将其保存到数据文件中。
不完全是CSV格式，但可以使用来自MySQL 客户端的 tee命令将输出保存到本地文件中：
tee foobar.txt
SELECT foo FROM bar;
您可以使用notee将其禁用。
SELECT … INTO OUTFILE …; 是它需要权限才能在服务器上写入文件。
为了扩大以前的答案，以下单行程序将单个表格导出为制表符分隔文件。 它适用于自动化，每天导出数据库等等。
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
以下是我的工作：
echo $QUERY | \
mysql -B $MYSQL_OPTS | \
perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
mail -s 'report' [email protected]
perl脚本（从其他地方狙击）可以很好地将制表符间隔字段转换为CSV。
使用Tim发布的解决方案，我创建了这个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的文件
在你的命令行中，你可以这样做：
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*
如果您正在使用的计算机上安装了PHP，则可以编写一个PHP脚本来执行此操作。 它要求PHP安装已安装MySQL扩展。
您可以从命令行调用PHP解释器，如下所示：
php --php-ini path/to/php.ini your-script.php
我包括--php-ini开关，因为您可能需要使用您自己的PHP配置来启用MySQL扩展。 在PHP 5.3.0+上，默认情况下启用该扩展，因此不再需要使用该配置启用它。
然后你可以像任何普通的PHP脚本一样编写你的导出脚本：
<?php
#mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());
$result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");
$result || die(mysql_error());
while($row = mysql_fetch_row($result)) {
$comma = false;
foreach ($row as $item) {
# Make it comma separated
if ($comma) {
echo ',';
} else {
$comma = true;
}
# Quote the quotes
$quoted = str_replace("\"", "\"\"", $item);
# Quote the string
echo "\"$quoted\"";
}
echo "\n";
}
?>
这种方法的优点是，它没有varchar和text字段的问题，其中包含换行符的文本。 这些字段被正确引用，并且这些字段中的换行符将被CSV阅读器解释为文本的一部分，而不是记录分隔符。 这是sed或之后很难纠正的事情。
微小的bash脚本，可以通过https://.com/a/5395421/2841607进行简单的CSV查询转储查询。
#!/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"
怎么样：
mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv
此外，如果您在Bash命令行上执行查询，我相信tr命令可用于将默认选项卡替换为任意分隔符。
$ echo "SELECT * FROM Employee" | mysql Database | tr '\t' ,
这个答案使用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 isn't much better, but this is an example
# http://.com/questions/12461484/is-it-secure-to-store-passwords-as-environment-variables-rather-than-as-plain-t
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://.com/a/17725590/2958070 about lineterminator
csv_writer = csv.writer(csvfile, lineterminator='\n')
csv_writer.writerows(results)
这很简单，它适用于任何不需要批处理模式或输出文件的任何内容：
select concat_ws(',',
concat('"', replace(field1, '"', '""'), '"'),
concat('"', replace(field2, '"', '""'), '"'),
concat('"', replace(field3, '"', '""'), '"'))
from your_table where etc;
说明：
- 在每个字段中替换“”替换为“”（field1，'''，'''''）
- 将每个结果用引号括起来 - > concat（'''，result1，'''）
- 在每个引用结果之间放置一个逗号 - > concat_ws（'，'，quoted1，quoted2，...）
而已！
除上述答案之外，还可以使用使用CSV引擎的MySQL表。
然后，您将在硬盘上拥有一个始终采用CSV格式的文件，您可以在不处理该文件的情况下进行复制。
Try this code:
SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5'
UNION ALL
SELECT column1, column2,
column3 , column4, column5 FROM demo
INTO OUTFILE '/tmp/demo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
for more: http://dev.mysql.com/doc/refman/5.1/en/select-into.html
