如何从 MySQL 命令行导出 CSV 文件

当前位置：主页 > 学无止境 > 数据库 > MySQL >

如何从 MySQL 命令行导出 CSV 文件

作者：迹忆客最近更新：2022/12/30 浏览次数：

逗号分隔值文件 (CSV) 是一种在应用程序之间传输数据的方式。 MySQL 等数据库和 Excel 等电子表格软件都支持通过 CSV 导入和导出，因此我们可以使用 CSV 文件在两者之间交换数据。

CSV 文件是纯文本，因此它们自然是轻量级的并且很容易从 MySQL 中导出。

从数据库服务器导出

如果我们有权访问运行 MySQL 的服务器，则可以使用 INTO OUTFILE 命令导出选择。

SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'

这会将 CSV 文件输出到 /tmp/mysqlfiles/table.csv，或者我们将其配置到的任何位置。我们需要确保运行 MySQL 的用户（通常是 mysql 或 root）拥有该目录的所有权和写入权限。

我们还需要确保 secure_file_priv 设置允许 MySQL 访问该目录。默认情况下，这会阻止 SQL 查询的读写访问。这是一件好事；如果我们的代码容易受到 SQL 注入的攻击，任何潜在的攻击者都只能访问 MySQL，而不能访问文件系统的其余部分。

我们可以通过编辑 MySQL 配置文件（通常位于 /etc/my.cnf）来将特定目录列入白名单，包括：

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

这将允许 MySQL 读取和写入 /tmp/mysqlfiles/ （我们必须使用 mkdir 创建）。一旦 MySQL 可以导出文件，我们就应该能够运行查询并输出 CSV 文件。

使用 ENCLOSED BY 设置，逗号将被正确转义，例如：

"3","Escape, this","also, this"

我们可以将其直接导入任何电子表格程序或其他软件。

从 MySQL 命令行导出 CSV 文件

请记住 ，导出的 CSV 文件不包含列标题，但列的顺序与SELECT 语句的顺序相同。此外，空值将导出为 N，这是预期的行为，但如果我们想更改此设置，我们可以通过在 SELECT 语句中将 ifnull(field, "") 包含在字段周围来修改选择。

从 MySQL 命令行导出

如果我们只能通过命令行访问 MySQL 实例，而不能访问服务器本身（例如当它不由我们管理时，在 Amazon RDS 的情况下），问题就有点棘手了。虽然我们可以在服务器上使用 FIELDS TERMINATED BY ',' 来生成以逗号分隔的列表，但 MySQL CLI 默认情况下将使用制表符分隔。

只需从命令行输入查询，并将其通过管道传输到文件：

$ mysql -u root -e "select * from database;" > output.tsv

由于 MySQL 输出以制表符分隔，因此称为 TSV 文件，表示“制表符分隔值”，并且可以在某些程序（如电子表格导入）中代替 CSV 文件。但它不是 CSV 文件，将其转换为 CSV 文件很复杂。

我们可以简单地用逗号替换每个选项卡，这会起作用，但如果输入数据中有逗号，则会导致失败。如果我们完全 100% 确定 TSV 文件中没有逗号（用 grep 检查），我们可以用 sed （关于该命令可以参考我们的 sed 教程）替换制表符：

$ sed "s/t/,/g" output.tsv > output.csv

但是如果你的数据中有逗号，你将不得不使用更长的正则表达式：

$ sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv

这将正确转义带引号的字段，从而解决逗号问题。

注意 ：制表符 t 不是标准的。在 macOS 和 BSD 上，它不可用，这会导致每个小写字母“t”乱七八糟，导致 sed 插入错误的逗号。要解决这个问题，我们需要使用文字制表符代替 t：
sed "s/  /,/g" output.tsv > output.csv

如果我们的输入数据包含制表符，那就不走运了，我们必须自己使用脚本语言生成一个 CSV 文件。

使用真正的编程语言手动完成

MySQL（和大多数数据库）旨在与之交互，因此我们可能已经将某种编程语言连接到 MySQL。大多数语言也可以写入磁盘，因此我们可以创建自己的 CSV 输出脚本，方法是直接从数据库中读取字段，正确转义它们，然后编写一个逗号分隔的文件。

迹忆客专注技术分享