The SELECT INTO OUTFILE statement allows you to specify the fields to be exported and are preferred when primary key fields do not need to be exported. You can use this statement in combination with the LOAD DATA INFILE statement to facilitate data import and export.
Background
This syntax is supported in OceanBase Database in MySQL mode. We recommend that you use OceanBase Database V2.2.40 and later in MySQL mode, where you can run commands by using MySQL Client and OBClient.
Notice
To export data from OceanBase Database, the client must be directly connected to the OceanBase Database instance.
Syntax
The following syntax is supported for a SELECT INTO OUTFILE statement:
SELECT [column_list] INTO OUTFILE {'/path/file' | 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESSID&access_key=$ACCESSKEY'}
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[FROM TABLENAME]
[WHERE condition]
[GROUP BY group_expression_list ]
[HAVING condition]]
[ORDER BY order_expression_list]
| Parameter | Required | Description | Example |
|---|---|---|---|
| SELECT [column_list] | Yes | The columns to be exported. To specify all data, use an asterisk (*). | select ID,name,score |
| '/PATH/FILE' or 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESSID&access_key=$ACCESSKEY' | Yes | The path of the exported file. Alibaba Cloud Object Storage Service (OSS) is supported.
Note |
into outfile '/home/admin/student.sql' |
| [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] | Yes | Specifies the field format in the output file.
|
terminated by ',' enclosed by '"' |
| [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] | Yes | LINES: specifies the characters to enclose each line in the output file.
|
lines terminated by '\n' indicates that a row ends with a line feed. |
| [FROM TABLENAME] [WHERE condition] [GROUP BY group_expression_list ] [HAVING condition]] [ORDER BY order_expression_list] | Yes | The table and content to be exported. WHERE: the query conditions. GROUP BY: the grouping attribute. ORDER BY: the sorting attribute. | from student |
Scenarios and examples
Data export example:
obclient> CREATE TABLE student (ID int primary key,name varchar(128),score int);
Query OK, 0 rows affected (0.04 sec)
obclient> INSERT INTO student VALUES(1,'lin',98),(2,'hei',90),(3,'ali',95);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM student;
+----+------+-------+
| ID | name | score |
+----+------+-------+
| 1 | lin | 98 |
| 2 | hei | 90 |
| 3 | ali | 95 |
+----+------+-------+
3 rows in set (0.00 sec)
obclient> SELECT ID,name,score INTO OUTFILE '/home/admin/student.sql'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' FROM student;
Query OK, 3 rows affected (0.01 sec)
Then you can check the information about the exported file in the /home/admin directory on your local device.
[root@***** /home/admin]cat student.sql
1,"lin",98
2,"hei",90
3,"ali",95
More information
You can use the LOAD DATA statement to import the files that are exported with OUTFILE. For more information, see Import data by using the LOAD DATA statement.
