Migrate data by using OUTFILE statements

#docslug#/oceanbase-database/oceanbase-database/V3.2.2/use-outfile-statements-to-migrate-data-1 This topic describes how to use a SELECT INTO OUTFILE statement to export data.

Compared with MySQLDump, SELECT INTO OUTFILE statements allow database administrators (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 importing and exporting. OceanBase Database is compatible with this syntax.

Notice

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

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]
      [LINES TERMINATED BY OPTIONALLY]
      [FROM  TABLENAME]
      [WHERE condition]
      [GROUP BY group_expression_list ]
      [HAVING condition]]
      [ORDER BY order_expression_list] 

Notes:

• TERMINATED BY specifies the separator between field values. For example, TERMINATED BY ',' specifies that field values are separated by 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 '*' specifies 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 row. For example, LINES TERMINATED BY ',' specifies that a row ends with a comma (,).