如何将 MySQL / MariaDB 的查询结果保存到文件

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

如何将 MySQL / MariaDB 的查询结果保存到文件

日常工作中,我们经常需要将查询结果保存出来,然后放在数据处理软件中跑分析,或者和小组同学共享数据协同处理。好在 MySQL / MariaDB 内置查询结果保存工具,只要注意一些细节和可能碰到的常见错误,即可游刃有余的将查询出的数据保存到文件。

本教程将详细讲解如何在 MySQL / MariaDB 中将查询结果保存到文件,以及可能碰到的几种错误的处理方法。

另外我之前写了两篇同样是 MySQL / MariaDB 导出类的教程,只是场景不同,也放在这里给大家参考。

我们开始吧。

一. 准备示例数据

CREATE DATABASE IF NOT EXISTS kalacloud_demo;

USE kalacloud_demo;

CREATE TABLE kalacloud_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO kalacloud_users (last_name,first_name,email) 
VALUES ('Chuan ','Jiang','HiJiangChuan@gmail.com'),
       ('Ke','Xie','xieke@qq.com'),
       ('Amei','Song','amei@163.com'),
       ('xiaoguo','lv','xiaoguo@sina.com');
select * from kalacloud_users;
# kalacloud.com

返回值:

准备数据返回值

本教程将详细讲解如何把这一段查询结果,保存到 txt / csv 以及讲解如何使用字段间分隔符、字段包围符、行间分隔符等分隔符。

二. MySQL / MariaDB 查询结果保存到 txt 文件

MySQL / MariaDB 内置了一个输出结果到文件的功能,我们只需要在 SELECT 结尾加上 INTO OUTFILE 保存路径 + 文件名

SELECT id, first_name, last_name,email
FROM kalacloud_users
INTO OUTFILE '/home/chuan/kalacloud_users_out_a.txt';

执行命令后,会看到返回结果

Query OK, 4 rows affected (0.008 sec)

特别提示:这里会报错 ERROR 1290 ,有两种可能性

  • MySQL 配置文件设置问题
  • 系统读写权限问题。

详情请参考本文第五条:INTO OUTFILE常见报错

然后我们可以在导出目录中看到 MySQL / MariaDB 生成的导出文件,我们可以用 cat 查看一下导出文件的内容:

into outfile 导出

cat 命令查看导出文件内容,平铺直叙,数据之间没有任何分割。

在工作中更多时候,需要导出 csv,接着我们来讲解如何导出 csv 文件。

三. INTO OUTFILE的参数及导出到 csv 文件

  • INTO OUTFILE:「导出文件信息」指定导出的目录、文件名及格式
  • FIELDS TERMINATED BY :「字段间分隔符」用于定义字段间的分隔符
  • OPTIONALLY ENCLOSED BY: 「字段包围符」定义包围字段的字符
  • LINES TERMINATED BY: 「行间分隔符」定义每行的分隔符

我们选择导出 *.csv 文件格式,然后分隔符用「 , 」字段包围符用「 " 」换行符为「 \n 」

SELECT id, first_name, last_name,email
FROM kalacloud_users
INTO OUTFILE '/tmp/kalacloud_users_out_b.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

into outfile 导出文件内容预览

我们可以看到这个 csv 文件只包括查询的信息,却没有列名,下面我们来说一下如何加上列名。

四. 在输出结果中加入「列名」增强可读性

我们输出的结果中并没有列名,没有列名的数据易读性很差,在多人协同中很容易出问题。

MySQL 并没有提供直接简单的方法给导出结果加上列名,但好消息是加列名并不复杂。

我们可以使用 UNION ALL 来选择列标题:

SELECT '用户ID', '姓氏', '名字', '电子邮箱'
UNION ALL
SELECT id, first_name, last_name,email
FROM kalacloud_users
INTO OUTFILE '/tmp/kalacloud_users_out_c.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

返回结果:

union-all 命令导出

特别提示:如何将服务器中的 txt / csv 文件下载到本地

scp [username]@[servername]:/tmp/kalacloud_users_out_c.csv /Users/kalacloud/Downloads/

五.INTO OUTFILE常见报错

1.ERROR 1290:无法写入

在导出文件时,我们可能会碰到「无法写入」的错误

MySQL:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

MariaDB:

ERROR 1 (HY000): Can't create/write to file '/home/user/pi/kalacloud_users_out_a.txt' (Errcode: 13 "Permission denied")

这是因为你的 MySQL / MariaDB 配置了--secure-file-priv 限制了导出文件的存放位置。

你可以使用以下命令来查看具体配置信息

show global variables like '%secure_file_priv%';

secure file priv 修改

  • secure_file_priv为 NULL 时,表示不允许导入或导出。
  • secure_file_priv为路径时(如/var/lib/mysql-files/ ),表示只允许在此路径目录中执行。
  • secure_file_priv没有值时,表示可在任意目录的导入导出。

你可以打开 my.cnf 或 my.ini,添加以下语句,重启 MySQL / MariaDB server 即可

secure_file_priv=''

重启 MySQL / MariaDB:

sudo systemctl restart mysql

2.ERROR 1086 :文件已存在

ERROR 1086 (HY000): File '/tmp/kalacloud_users_out_c.csv' already exists

这个错误是 INTO OUTFILE 指向的目录中含有相同的文件名的文件,请更换文件名保存即可。

六. 使用「卡拉云」解决结果分享与保存问题

MySQL / MariaDB 数据导出操作,多数场景还是为了数据展示、分析、协同共享等产品和运营层面的应用场景。比如后端工程师接到产品需求,协助导出某类数据等场景。

如果这类需求频繁出现,推荐使用卡拉云,卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。不仅可以像命令行一样灵活,还可根据自己的工作流,定制开发。

卡拉云导出文件

卡拉云可直接添加导出按钮,导出适用于各类分析软件的数据格式,方便快捷。立即开通卡拉云,导出你的查询结果。

快速搭建企业内部工具

卡拉云可根据公司工作流需求,轻松搭建数据看板,并且可分享给组内的小伙伴共享数据

仅需拖拽一键生成前端代码,简单一行代码即可映射数据到指定组件中。

卡拉云支持的常见数据库及 API

卡拉云可快速接入的常见数据库及 API

无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,欢迎试用卡拉云

更多数据库相关教程可访问 卡拉云 查看。如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云联合创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9