mysql将查询结果导出到txt/csv文件 | 您所在的位置:网站首页 › mysql数据库写入文本 › mysql将查询结果导出到txt/csv文件 |
日常工作中,我们经常需要将查询结果保存出来,然后放在数据处理软件中跑分析,或者和小组同学共享数据协同处理。好在 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','[email protected]'), ('Ke','Xie','[email protected]'), ('Amei','Song','[email protected]'), ('xiaoguo','lv','[email protected]'); 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 sec) 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';返回结果 在导出文件时,我们可能会碰到「无法写入」的错误 MySQL: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementMariaDB: 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 限制了导出文件的存放位置。另外,如果你检查secure_file_priv的配置是正确的,仍存在报错Permission denied,此时,你返回到存放导出文件内容的上一个文件夹路径chmod 777 data即可,将最大的授权给到存放文件内容的文件夹 你可以使用以下命令来查看具体配置信息: show global variables like '%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 指向的目录中含有相同的文件名的文件,请更换文件名保存即可。
|
CopyRight 2018-2019 实验室设备网 版权所有 |