有没有一种简单的方式可以从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
当需要将许多列用引号括起来或结果中有需要转义的引号时,情况会变得混乱。
虽然不完全符合CSV格式,但是MySQL客户端中的tee
命令可以用于将输出保存到本地文件中:
tee foobar.txt
SELECT foo FROM bar;
您可以使用notee
来禁用它。
SELECT … INTO OUTFILE …;
的问题在于它需要在服务器上具有写文件的权限。
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'
我的经验:
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;
使用Tim Harding发布的解决方案,我创建了这个Bash脚本来简化该过程(需要根密码,但您可以轻松修改脚本以请求任何其他用户):
#!/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
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
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
等。那里提到了一些关于行结尾的细微差别,我没有评估。
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 $'\t' mydb.db '.import /dev/stdin mycsvtable'
2>/dev/null
是用来屏蔽命令行密码警告的。这很糟糕且不美观。它只适用于特定情况,即您只有一个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'),
...
这是一个小巧的Bash脚本,用于对CSV转储进行简单查询。它受到了 Tim Harding的回答的启发。
#!/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"
secure-file-priv
的错误,即使将目标文件位置移动到C:\ProgramData\MySQL\MySQL Server 8.0\Uploads
内,也无法正常工作:
SELECT * FROM attendance INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
那么,你只需要将查询中的 \
(反斜杠)改成 /
(正斜杠)就可以解决问题。
然后它就能正常工作了!
例如:
SELECT * FROM attendance INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
每次成功运行查询时,都会生成新的 CSV 文件!很酷,对吧?
REPLACE()
函数来转义引号。 - dsm