Use the `SELECT INTO OUTFILE` statement to export data

This topic describes how to use the SELECT INTO OUTFILE statement to export data. The SELECT INTO OUTFILE statement allows you to specify the fields to be exported, and is preferred when the primary key columns do not need to be exported. You can use this statement in combination with the LOAD DATA INFILE statement to facilitate data import and export.

Background information

OceanBase Database is compatible with the SELECT INTO OUTFILE statement.

Syntax

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']

Examples

The following takes exporting data to a local device as an example to describe how to export data.

1. Create a table named tbl1 in the test database in the mysql001 tenant and insert data into the table.

   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
   

2. Set the export path.

   Set the system variable secure_file_priv to specify the path that can be accessed for file import or export.

   Notice

   For security reasons, you can only connect to the database through a local socket to execute the SQL statement for setting secure_file_priv. For more information, see secure_file_priv.

   1. Log in to the OBServer node to connect to.

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

   2. Run the following command to connect to the mysql001 tenant through a local Unix socket:

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. Set the export path to /home/admin.

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

3. Reconnect to the database and execute the SELECT INTO OUTFILE statement to export data from the tbl1 table. The name of the exported file is tbl1.sql. The field values are separated with a comma (,). The values of the string type are enclosed within a pair of double quotation marks ("). The end symbol is a line feed.

   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
   

4. Log in to the OBServer node and check the information about the exported file in the /home/admin directory on your local device.

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

More information

You can use the LOAD DATA statement to import the files that are exported by using SELECT INTO OUTFILE. For more information, see Use the LOAD DATA statement to import data.