由于我经常遇到这个确切的问题(希望之前我错过了什么...),所以最终决定花时间写一个小型Gist以将MySQL查询导出为CSV文件,有点像https://dev59.com/EnE85IYBdhLWcg3wQxLG#28168869,但基于PHP并具有更多选项。对于我的用例非常重要,因为我需要能够微调CSV参数(分隔符、NULL值处理)并且文件实际上需要是有效的CSV,因此简单的CONCAT
是不够的,因为如果值包含换行符或CSV分隔符,则不会生成有效的CSV文件。
注意:需要在服务器上安装PHP!
(可以通过php -v
检查)
通过以下方式“安装”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
下载gist的内容,检查校验和并使其可执行。
用法示例
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"
生成文件
/tmp/result.csv
并包含内容。
foo,bar
1,2
参考帮助
./mysql2csv --help
Helper command to export data for an arbitrary mysql query into a CSV file.
Especially helpful if the use of "SELECT ... INTO OUTFILE" is not an option, e.g.
because the mysql server is running on a remote host.
Usage example:
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"
cat /tmp/result.csv
Options:
-q,--query=name [required]
The query string to extract data from mysql.
-h,--host=name
(Default: 127.0.0.1) The hostname of the mysql server.
-D,--database=name
The default database.
-P,--port=name
(Default: 3306) The port of the mysql server.
-u,--user=name
The username to connect to the mysql server.
-p,--password=name
The password to connect to the mysql server.
-F,--file=name
(Default: php://stdout) The filename to export the query result to ('php://stdout' prints to console).
-L,--delimiter=name
(Default: ,) The CSV delimiter.
-C,--enclosure=name
(Default: ") The CSV enclosure (that is used to enclose values that contain special characters).
-E,--escape=name
(Default: \) The CSV escape character.
-N,--null=name
(Default: \N) The value that is used to replace NULL values in the CSV file.
-H,--header=name
(Default: 1) If '0', the resulting CSV file does not contain headers.
--help
Prints the help for this command.