Migrate data from SQL files to OceanBase Database

This topic describes how to use a SELECT INTO OUTFILE statement to export data.

Compared with MySQLDump, SELECT INTO OUTFILE statements allow database adminiistrators (DBAs) to specify the fields to be exported and are preferred when primary key fields do not need to be exported. You can use these statements in combination with the LOAD DATA INFILE statements to facilitate data import and export. OceanBase Database is compatible with this syntax.

Notes

To import data into and export data from OceanBase Database, the client must be connected to the OceanBase Database instance directly.

The system parameter secure_file_priv is used to specify restrictions on LOAD DATA, SELECT …OUTFILE, and LOAD_FILE(). Corresponding privileges must be set for exporting data.

• When secure_file_priv is set to NULL, MySQLDump cannot import or export data.
• When secure_file_priv is set to /tmp, MySQLDump can import or export data only in the /tmp directory.
• When secure_file_priv is unspecified, the import and export of MySQLDump is not restricted.

Syntax

The following syntax is supported for a SELECT INTO OUTFILE statement:

SELECT [column_list] INTO  '/PATH/FILE'
      [TERMINATED BY OPTIONALLY]
      [ENCLOSED BY OPTIONALLY]
      [ESCAPED BY OPTIONALLY]  

Notes:

• TERMINATED BY: specifies the separator between field values. For example, TERMINATED BY ',' specifies that field values are separated with commas (,).

• ENCLOSED BY: specifies the symbol for enclosing character values in a file. For example, ENCLOSED BY ' " ' specifies that the character values in a file are enclosed in double quotation marks (") and ENCLOSED BY ' " ' OPTIONALLY specifies that all values in a file are enclosed in the double quotation marks (").

• ESCAPED BY: specifies the escape character. For example, ESCAPED BY '*' indicates that the asterisk (*) but not the backslash () is used as the escape character. The backslash () is the default escape character.

• LINES TERMINATED BY: specifies the end of a line. For example, LINES TERMINATED BY ',' indicates that a line ends with a comma (,).

Example

Use the SELECT INTO OUTFILE statement to export data.

obclient> SELECT * INTO OUTFILE '/tmp/demo01.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM student;