SELECT INTO

Purpose

You can use this statement to store the query results in a variable or file.

• SELECT ... INTO OUTFILE stores the result set in an external file in the specified output format.

• SELECT ... INTO DUMPFILE writes a single line without any format to an external file.

• SELECT ... INTO var_list stores the result set in variables.

Required privileges

To execute the SELECT INTO statement, you must have the FILE privilege and the SELECT privilege on the corresponding table. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Here is an example:

Grant the FILE privilege to a user. You can use the following command:

GRANT FILE ON *.* TO user_name;

Here, user_name is the username of the account that executes the SELECT INTO statement.

Syntax

select_stmt INTO
    {OUTFILE 'file_name' [ {CHARSET | CHARACTER SET} charset_name] [field_opt] [line_opt] [file_opt]
    | DUMPFILE 'file_name'
    | into_var_list}
;

field_opt:
    {COLUMNS | FIELDS} field_term_list

field_term_list:
  field_term [, field_term ...]

field_term:
    {[OPTIONALLY] ENCLOSED | TERMINATED | ESCAPED} BY string

line_opt:
    LINES line_term_list

line_term_list:
    line_term [, line_term ...]

line_term:
    {STARTING | TERMINATED} BY string

file_opt:
    file_option [, file_option ...]

file_option:
    SINGLE [=] {TRUE | FALSE}
    | MAX_FILE_SIZE [=] {int | string}

Parameters

field_term

• [OPTIONALLY] ENCLOSED BY string: the characters for enclosing field values. By default, the field values are not enclosed by any characters. For example, ENCLOSED BY '"' specifies that character values are enclosed within a pair of double quotation marks (""). If the OPTIONALLY keyword is used, only values of the string type are enclosed within the specified symbols.
• TERMINATED BY string: the separator between field values. For example, TERMINATED BY ',' specifies that field values are separated with commas (,).
• ESCAPED BY string: the escape character, which is used to process special characters or parse data in special formats. The backslash (\) is the default escape character.

line_term

• STARTING BY string: the character to start each line. By default, the character is not specified.
• TERMINATED BY string: the character to end each line. The default value is a line feed. For example, ... LINES TERMINATED BY '\n' ... specifies that a row ends with a line feed.

file_option

• SINGLE [=] {TRUE | FALSE}: specifies whether to export data as a single file or multiple files.

  • SINGLE [=] TRUE: exports data as a single file. This is the default value.
  • SINGLE [=] FALSE: exports data as multiple files.

  Notice

  When the degree of parallelism (DOP) is greater than 1 and SINGLE = FALSE, you can export data as multiple files to achieve parallel reading and writing for a higher export speed.

• MAX_FILE_SIZE [=] {int | string}: the maximum size of a single file when SINGLE = FALSE.

Examples

1. Set a path for the exported file.

   To export a file, you need to set the system variable secure_file_priv to configure the path that can be accessed for export.

   Notice

   For security reasons, when you set the system variable secure_file_priv, you can connect to the database only through a local socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.

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

      ssh admin@xxx.xxx.xxx.xxx
      

   2. Execute the following statement 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 path for the exported file to /home/admin/test_data.

      SET GLOBAL secure_file_priv = "/home/admin/test_data";
      

   4. Log out of the database.

2. Reconnect to the database and execute the SELECT INTO OUTFILE statement to export data. Specify to separate two field values with a comma (,), enclose a field value of the string type in double quotation marks ("), and end each line with a line feed.

   • Write a single file serially and specify the file name as test_tbl1.csv.

     SELECT /*+parallel(2)*/ * FROM test_tbl1
     INTO OUTFILE '/home/admin/test_data/test_tbl1.csv'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n';
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   • Write multiple files in parallel without specifying the file name prefix. That is, the file names use the default prefix data. Set the maximum size of each file to 4 MB.

     SELECT /*+parallel(2)*/ * FROM test_tbl1
       INTO OUTFILE '/home/admin/test_data/'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       SINGLE = FALSE MAX_FILE_SIZE = '4MB';
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   • Write multiple files in parallel. Set the prefix of the file names to dd2024 and the maximum size of each file to 4 MB.

     SELECT /*+parallel(2)*/ * FROM test_tbl1
       INTO OUTFILE '/home/admin/test_data/dd2024'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       SINGLE = FALSE MAX_FILE_SIZE = '4MB';
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   Note

   • When multiple export tasks with the same destination path are executed at the same time, errors may occur or only part of the data may be exported. Therefore, you need to plan the export paths reasonably.
     Here is an example:
     When SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; and SELECT /*+parallel(2)*/ * FROM t2 INTO OUTFILE 'test/data' SINGLE = FALSE; are executed at the same time, an error may be reported due to the same export file name. In this case, we recommend that you set the export paths to test/data1 and test/data2 respectively.
   • When SINGLE = FALSE and the export task fails due to reasons such as file already exist, you can clear all files with the same file name prefix as the exported files in the export directory, or delete the export directory and create a new one, and then perform the export operation again.
     Here is an example:
     When the execution of SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; fails, you can delete all files with the file name prefix data under the test directory, or delete the test directory and create it again, and then try the export operation again.

3. Log on to the server where the OBServer node resides and check the exported files under the directory /home/admin/test_data of the node.

   [xxx@xxx /home/admin/test_data]# ls
   

   The return result is as follows:

   data_0_0_0  data_0_1_0  dd2024_0_0_0  dd2024_0_1_0  test_tbl1.csv
   

   Here, test_tbl1.csv is the serially exported single file. data_0_0_0 and data_0_1_0 are the parallelly exported files without a specified file name prefix. dd2024_0_0_0 and dd2024_0_1_0 are the parallelly exported files with the specified file name prefix dd2024.

References

Export data by using OUTFILE statements