Migrate data by using OUTFILE statements

The SELECT INTO OUTFILE statement is commonly used by DBAs for exporting data. Compared with MySQLDump, the SELECT INTO OUTFILE statement allows DBAs to specify the fields to be exported and is preferred when primary key fields do not need to be exported. You can use the statement together with the LOAD DATA INFILE statement 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 syntax of the SELECT INTO OUTFILE statement is as follows:

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] 

In the preceding syntax:

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

• ENCLOSED BY: specifies the symbol for enclosing character values in a file. For example, ENCLOSED BY ' " ' indicates that the character values in a file are enclosed in double quotation marks (") and ENCLOSED BY ' " ' OPTIONALLY indicates that all character 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 row. For example, LINES TERMINATED BY ',' indicates that a row ends with a comma (,).

Example

The following is data in the sample table student :

obclient> SELECT * FROM student;
+------+------+
| ID   | AGE  |
+------+------+
|    1 |    7 |
|    3 |    8 |
|    4 |    9 |
+------+------+

OceanBase Database is compatible with the SELECT INTO OUTFILE statement in MySQL. You can run the following statement in a client supported by OceanBase Database to export the data in the table student to a TXT file in the specified path:

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

OceanBase Database is also compatible with the LOAD DATA INFILE statement in MySQL. You can run the following statement in a client supported by OceanBase Database to import the data in the TXT file to the target table student :

obclient> LOAD DATA INFILE '/tmp/demo01.txt' INTO TABLE student FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';