SELECT INTO

Description

You can execute this statement to store the query result in a variable or a file. The statements are described as follows:

• The SELECT ... INTO OUTFILE statement stores the result set in an external file and allows you to specify the output format.

• The SELECT ... INTO DUMPFILE statement writes a single row without format to an external file.

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

Privilege requirements

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

Here is an example:

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

GRANT FILE ON *.* TO user_name;

In this example, user_name is the username of the user who needs to execute 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}

Parameter explanation

field_term

• [OPTIONALLY] ENCLOSED BY string: specifies the symbols that wrap the field values. The default value is no enclosing symbols. For example, ENCLOSED BY '"' specifies to enclose the field values with double quotation marks. If the OPTIONALLY keyword is used, only string-type values are enclosed with the specified symbols.
• TERMINATED BY string: specifies the delimiter between field values. For example, TERMINATED BY ',' specifies to use comma as the delimiter between field values.
• ESCAPED BY string: specifies the escape character to handle special characters or parse data in special formats. The default escape character is the backslash (\).

line_term

• STARTING BY string: specifies the start character of each row.
• TERMINATED BY string: specifies the end character of each row. The default value is the line break character. For example, ... LINES TERMINATED BY '\n' ... specifies to use the line break character as the end character of each row.

file_option

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

  • SINGLE [=] TRUE: the default value, which specifies to export data to a single file.

  • SINGLE [=] FALSE: specifies to export data to multiple files.

    Notice

    When the parallel degree is greater than 1 and SINGLE = FALSE, you can export data to multiple files to achieve parallel reading, parallel writing, and increased export speed.

• MAX_FILE_SIZE [=] {int | string}: the maximum size of a single file when you export data, and it takes effect only when SINGLE = FALSE.

Examples

1. Set the path for exporting files.

   To export files, you must first set the system variable secure_file_priv to a path that the export file can access.

   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 in to the OBServer node where you want to export files.

      ssh admin@xxx.xxx.xxx.xxx
      

   2. Connect to the mysql001 tenant by using a local Unix socket.

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

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

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

   4. Log out.

2. Reconnect to the database and use the SELECT INTO OUTFILE statement to export data. Use a comma to separate the values of the two fields; enclose the values of the string type in "; and use line breaks as the end markers.

   • Export data to a single file named 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
     

   • Export data to multiple files without specifying file names (namely, using the default prefix data for the file names). Make sure that the size of each file does not exceed 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
     

   • Export data to multiple files. Specify the prefix of the file names as dd2024. Make sure that the size of each file does not exceed 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 are exporting data to the same path at the same time, errors may occur or only part of the data may be exported. In this case, try to set different export paths.
     For example:
     SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; and SELECT /*+parallel(2)*/ * FROM t2 INTO OUTFILE 'test/data' SINGLE = FALSE; may fail with an error if the two statements attempt to export data to files with the same name.
   • After the SINGLE = FALSE condition is met, if the export fails due to the file already existing, you can delete all files with the same prefix as the export target in the export directory or directly delete the export directory and then rebuild it. After that, you can attempt to export the data again.
     For example:
     After the SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; statement fails, you can delete all files with the data prefix in the test directory or directly delete the test directory and then rebuild it. Then, you can try to export the data again.

3. Log in to the server and view the exported files in the /home/admin/test_data directory on the OBServer 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 file name in the example of exporting data to a single file; data_0_0_0 and data_0_1_0 are the file names in the example of exporting data to multiple files without specifying file names; and dd2024_0_0_0 and dd2024_0_1_0 are the file names in the example of exporting data to multiple files with the prefix dd2024 for the file names.

References

Export data by using the OUTFILE statement