分布式数据库学习Note214：OceanBase社区版中，使用 OUTFILE 语句导出数据

模式	建议使用的 OceanBase 数据库版本	建议使用的客户端
MySQL 模式	V2.2.40 及以上	MySQL Client、OBClient
Oracle 模式	V2.2.40 及以上	OBClient

模式

建议使用的 OceanBase 数据库版本

建议使用的客户端

MySQL 模式

V2.2.40 及以上

MySQL Client、OBClient

Oracle 模式

V2.2.40 及以上

OBClient

语法

SELECT column_list_option INTO OUTFILE file_route_option [format_of_field_option] [start_and_end_option] FROM table_name_list [WHERE where_conditions] [GROUP BY group_by_list [HAVING having_search_conditions]] [ORDER BY order_expression_list] column_list_option: column_name[,column_name]... file_route_option: '/path/file' | 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESSKEY' format_of_field_option: {FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] start_and_end_option: LINES [STARTING BY 'string'] [TERMINATED BY 'string']

参数	是否必填	描述	示例
column_list_option	是	导出的列选项。如果要选中全部数据可以用 `*` 表示。 `column_name`：列名称。	`SELECT col1,col2,col3 ...`
file_route_option	是	选择导出的文件路径，支持导出到阿里云 OSS 中。说明由于阿里云 OSS 有文件大小的限制，对于超过 5 GB 的文件，导出到 OSS 时会被拆分成多个文件，每个文件小于 5 GB。	`... INTO OUTFILE '/home/admin/student.sql' ...`
format_of_field_option	否	导出字段格式选项。指定输出文件中各个字段的格式，通过 `FIELDS` 或 `COLUMNS` 子句来指定。 `TERMINATED BY`：用来指定字段值之间的符号。例如，`TERMINATED BY ','` 指定了逗号作为两个字段值之间的标志。 `ENCLOSED BY`：用来指定包裹字段值的符号。例如，`ENCLOSED BY '"'` 表示字符值放在双引号之间。如果使用了 `OPTIONALLY` 关键字，则仅对字符串类型的值使用指定字符包裹。 `ESCAPED BY`：用来指定转义字符。例如，`ESCAPED BY ''` 表示将星号（）指定为转义字符来取代默认的转义字符（\）。	`... TERMINATED BY ',' ENCLOSED BY '"' ...`
start_and_end_option	否	导出数据行的开始和结束符选项。指定输出文件中每一行的开始和结束字符，通过 `LINES` 子句设置。 `STARTING BY`：指定每一行开始的字符。 `TERMINATED BY`：指定每一行的结束字符。	`... LINES TERMINATED BY '\n' ...` 表示一行将以换行符作为结束标志。
FROM table_name_list	是	指定选择数据的对象。	`... FROM tbl1,tbl2 ...`
WHERE where_conditions	否	指定筛选条件，查询结果中仅包含满足条件的数据。	`... WHERE col1 > 100 ...`
GROUP BY group_by_list	否	指定分组的字段，通常与聚合函数配合使用。说明 `SELECT` 子句后面的所有列中，没有使用聚合函数的列，必须出现在 `GROUP BY` 子句后面。	`... GROUP BY col1,col2 ...`
HAVING having_search_conditions	否	筛选分组后的各组数据。`HAVING` 子句与 `WHERE` 子句类似，但是 `HAVING` 子句可以使用累计函数（如 `SUM`、`AVG` 等）。	`... HAVING SUM(col1) < 160 ...`
ORDER BY order_expression_list	否	指定结果集按照一个列或者多个列用来 ASC 或 DESC 显示查询结果。不指定 ASC 或者 DESC 时，默认为 ASC。 `ASC`：表示升序。 `DESC`：表示降序。	`... ORDER BY col1,col2 DESC ...`

参数

是否必填

描述

示例

column_list_option

是

导出的列选项。如果要选中全部数据可以用 * 表示。
column_name：列名称。

SELECT col1,col2,col3 ...

file_route_option

是

选择导出的文件路径，支持导出到阿里云 OSS 中。

说明
由于阿里云 OSS 有文件大小的限制，对于超过 5 GB 的文件，导出到 OSS 时会被拆分成多个文件，每个文件小于 5 GB。

... INTO OUTFILE '/home/admin/student.sql' ...

format_of_field_option

否

导出字段格式选项。指定输出文件中各个字段的格式，通过 FIELDS 或 COLUMNS 子句来指定。

TERMINATED BY：用来指定字段值之间的符号。例如，TERMINATED BY ',' 指定了逗号作为两个字段值之间的标志。
ENCLOSED BY：用来指定包裹字段值的符号。例如，ENCLOSED BY '"' 表示字符值放在双引号之间。如果使用了 OPTIONALLY 关键字，则仅对字符串类型的值使用指定字符包裹。
ESCAPED BY：用来指定转义字符。例如，ESCAPED BY '*' 表示将星号（*）指定为转义字符来取代默认的转义字符（\）。

... TERMINATED BY ',' ENCLOSED BY '"' ...

start_and_end_option

否

导出数据行的开始和结束符选项。指定输出文件中每一行的开始和结束字符，通过 LINES 子句设置。

STARTING BY：指定每一行开始的字符。
TERMINATED BY：指定每一行的结束字符。

... LINES TERMINATED BY '\n' ... 表示一行将以换行符作为结束标志。

FROM table_name_list

是

指定选择数据的对象。

... FROM tbl1,tbl2 ...

WHERE where_conditions

否

指定筛选条件，查询结果中仅包含满足条件的数据。

... WHERE col1 > 100 ...

GROUP BY group_by_list

否

指定分组的字段，通常与聚合函数配合使用。

说明
SELECT 子句后面的所有列中，没有使用聚合函数的列，必须出现在 GROUP BY 子句后面。

... GROUP BY col1,col2 ...

HAVING having_search_conditions

否

筛选分组后的各组数据。HAVING 子句与 WHERE 子句类似，但是 HAVING 子句可以使用累计函数（如 SUM、AVG 等）。

... HAVING SUM(col1) < 160 ...

ORDER BY order_expression_list

否

指定结果集按照一个列或者多个列用来 ASC 或 DESC 显示查询结果。不指定 ASC 或者 DESC 时，默认为 ASC。

ASC：表示升序。
DESC：表示降序。

... ORDER BY col1,col2 DESC ...

示例

本文以数据导出到设备本地为例，提供数据的导出示例。

在租户 mysql001 的 test 库中创建表 tbl1 并插入数据。

obclient [test]> CREATE TABLE tbl1(col1 INT PRIMARY KEY,col2 varchar(128),col3 INT);
Query OK, 0 rows affected

obclient [test]> INSERT INTO tbl1 VALUES(1,'one',80),(2,'two',90),(3,'three',100);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient [test]> SELECT * FROM tbl1;
+------+-------+------+
| col1 | col2  | col3 |
+------+-------+------+
|    1 | one   |   80 |
|    2 | two   |   90 |
|    3 | three |  100 |
+------+-------+------+
3 rows in set

设置导出的文件路径。

使用管理员用户（MySQL 模式：root；Oracle 模式：SYS）登录到集群对应的租户，设置系统变量 secure_file_priv，配置导入或导出文件时可以访问的路径。

系统变量 secure_file_priv 用于控制导入或导出到文件时可以访问的路径。默认值为 NULL，表示导入、导出被禁用。详细信息，请参见 secure_file_priv（MySQL 模式）和 secure_file_priv（Oracle 模式）。

注意

由于安全原因，设置系统变量 secure_file_priv 时，只能使用本地 Client （即：OBClient 所在机器与连接的 OBServer 节点的 IP 地址相同。）执行修改该全局变量的 SQL 语句。

登录到要连接的 OBServer 节点。

[xxx@xxx /home/admin]# ssh admin@xxx.xxx.xxx.xxx

使用 OBClient 客户端通过直连的方式连接租户 mysql001。

[admin@xxx /home/admin]# obclient -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p -A
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221493926
Server version: OceanBase 4.0.0.0 (r100000302022111120-7cef93737c5cd03331b5f29130c6e80ac950d33b) (Built Nov 11 2022 20:38:33)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]>

设置导出路径为 /home/admin。

obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected

使用 SELECT INTO OUTFILE 语句导出表 tbl1 中的数据。导出文件名为 tbl1.sql；指定逗号作为两个字段值之间的标志；对字符串类型的值使用 " 字符包裹；使用换行符作为结束标志。

obclient [test]> SELECT * INTO OUTFILE '/home/admin/tbl1.sql' 
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    FROM tbl1;
Query OK, 3 rows affected

登录机器，在设备本地的 /home/admin 目录下查看导出的文件信息。

[xxx@xxx /home/admin]# cat tbl1.sql
1,"one",80
2,"two",90
3,"three",100

分布式数据库学习Note214：OceanBase社区版中，使用 OUTFILE 语句导出数据

背景信息

注意

语法

示例

注意

更多信息

评论