mysql将查询结果导出到txt/csv文件
原创
©著作权归作者所有:来自51CTO博客作者Lucky_Syw的原创作品,请联系作者获取转载授权,否则将追究法律责任
日常工作中,我们经常需要将查询结果保存出来,然后放在数据处理软件中跑分析,或者和小组同学共享数据协同处理。好在 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
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
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';
我们可以看到这个 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';
返回结果
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
- 为路径时(如/var/lib/mysql-files/ ),表示只允许在此路径目录中执行。
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