软件开发培训班 >> 编程开发 >> MySQL


MySQL?如何将查询结果导出到文件(select?…?into?Statement)

我们经常会遇到需要将SQL查询结果导出到文件，以便后续的传输或数据分析的场景 。为了满足这个需求，MySQL的select语句提供了into子句可以将的查询结果直接导出到文本文件 。本文就MySQL中select…into的用法进行演示 。

一、select…into语句简介

select…into语句不仅仅能够将查询结果导出到csv文件，实际上它有3种用法：

  • select … into @var_list from …. 将查询结果写入到一组变量
  • select … into outfile from …. 将数据写入操作系统文件，可以自定义数据格式（分隔符、包裹符、转义符、换行符等） 。
  • select … into dumpfile from …. 将单一行写入文件，没有任何格式化

1.1 基本语法

根据官方文档，into子句可以出现在以下3个位置 。虽然3个位置都符合语法，但select语句中至多只能有1个into语句（位置三选一）:

  • 查询字段之后，from子句前
  • 锁定子句前（未来版本即将废弃）
  • 语句的最后

这里推荐将into子句放在位置3，即语句的末尾，这样相对普通的select语句更为接近，更容易理解，后面的示例也将采用这种写法 。

二、用法示例

下面演示into子句三种用法 。

2.1 将查询结果保存到变量

into子句的一个常用场景就是将查询结果暂时保存到变量中，以便后续查询或使用，唯二要注意的点是变量的数量要和返回列数量匹配，并且最多只能返回一行数据 。

以示例数据库employees中的employees表为例，查询3个结果，分别保存到3个变量中：

select emp_no,first_name,hire_date from employees limit 1 into @emp_no, @first_name, @hire_date;
select @emp_no, @first_name, @hire_date;

这里用limit 1子句限制返回的结果只有1行，否则会报错 。

2.2 将查询结果保存到文本文件

into outfile子句可以将查询结果导出到文本文件，虽然并不一定要是CSV格式，但大多数情况下我们都会选择这种格式 。

要将MySQL中的数据写入到操作系统的文件中， 首先需要具有FILE权限 。而且为了安全需要配置参数secure_file_priv，这个参数是限制MySQL可以写入文件的目录：

show variables like 'secure_file_priv';

我们在普通的select语句最后添加into outfile ‘/path/file_name’;即可将查询结果写入文件，这里的path就是参数secure_file_priv定义的目录（文件不能已存在）：

select *from employees where emp_no<=10010 into outfile '/opt/mysql8.0.35/mysql-files/employees.txt';

下面是导出文件内容，其实into outfile文件还隐式在后面附件了2个子句：

fields terminated by '	' enclosed by '' escaped by '\'
lines terminated by '
' starting by ''

  • fields 表示字段属性，terminated by ‘ ’ 以制表符分割字段，enclosed by ‘’ 不包裹字段，escaped by ‘\’ 反斜线表示转义符（这里2个反斜线，第一个是转义符，第二个是反斜线）
  • lines 表示行属性，terminated by ‘ ’ 代表换行符，starting by ‘’ 行的起点字符是空 。

如果要导出CSV格式的文件，并且以双引号"包裹字段，那么只需要增加一个fields terminated by ‘,’ enclosed by '"'子句即可，其他的保持默认：

select *from employees where emp_no<=10010 into outfile '/opt/mysql8.0.35/mysql-files/employees.csv' fields terminated by ',' encolsed by '"';

可以看到这次导出结果就是以逗号分割，以双引号包裹字段的数据：

2.3 将查询结果保存到dumpfile

into dumpfile 子句可以将一行数据导出到文件，但是它不会做任何的分割，格式的定义，转义等操作 。相对应用场景较少，这个操作通常用于将大型的BLOB字段保存到文件中，了解即可：

select *from employees limit 1 into dumpfile '/opt/mysql8.0.35/mysql-files/employees.dump';

注意select语句只能返回一行结果，否则会报错 。

导出的结果如下，可以看到数据没有任何分隔，都连在了一起，连换行都没有（提示符和数据显示在同一行）：

到此这篇关于MySQL 将查询结果导出到文件（select … into Statement）的文章就介绍到这了,更多相关mysql查询结果导出到文件内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！