如何从 mysql 命令行导出 csv 文件-ag捕鱼王app官网

当前位置：ag捕鱼王app官网 > > 数据库 > 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 输出脚本，方法是直接从数据库中读取字段，正确转义它们，然后编写一个逗号分隔的文件。

如何从 mysql 命令行导出 csv 文件-ag捕鱼王app官网