OUTFILEステートメントを使用したデータのエクスポート

SELECT INTO OUTFILE ステートメントは、一般的に使用されるデータエクスポート方法です。このステートメントでは、エクスポートするフィールドを制限できるため、主キーを含むフィールドをエクスポートする必要がない場合に非常に適しています。また、LOAD DATA INFILE ステートメントと組み合わせてデータをインポートすることで、便利なデータのインポート・エクスポートが可能になります。

背景情報

OceanBaseデータベースはこの構文と互換性があります。

構文

SELECT column_list_option
INTO OUTFILE file_route_option
    [format_of_field_option]
    [start_and_end_option]
FROM table_name_list
[WHERE where_conditions]
[GROUP BY group_by_list [HAVING having_search_conditions]]
[ORDER BY order_expression_list]

column_list_option:
    column_name[,column_name]...

file_route_option:
    '/path/file'
    | 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESSKEY'

format_of_field_option:
    {FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']

start_and_end_option:
    LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']

例

本記事では、データをデバイスのローカルにエクスポートする例を挙げて説明します。

1. テナント mysql001 の test データベースにテーブル tbl1 を作成し、データを挿入します。

   obclient [test]> CREATE TABLE tbl1(col1 INT PRIMARY KEY,col2 varchar(128),col3 INT);
   Query OK, 0 rows affected
   
   obclient [test]> INSERT INTO tbl1 VALUES(1,'one',80),(2,'two',90),(3,'three',100);
   Query OK, 3 rows affected
   Records: 3  Duplicates: 0  Warnings: 0
   
   obclient [test]> SELECT * FROM tbl1;
   +------+-------+------+
   | col1 | col2  | col3 |
   +------+-------+------+
   |    1 | one   |   80 |
   |    2 | two   |   90 |
   |    3 | three |  100 |
   +------+-------+------+
   3 rows in set
   

2. エクスポートファイルのパスを設定します。

   システム変数 secure_file_priv を設定して、インポートまたはエクスポートファイルへのアクセスパスを構成します。

   注意

   セキュリティ上の理由により、システム変数 secure_file_priv を設定する際は、ローカルソケット経由でデータベースに接続し、このグローバル変数を変更するSQL文を実行する必要があります。詳細については、secure_file_privを参照してください。

   1. 接続先のOBServerノードにログインします。

      [xxx@xxx /home/admin]# ssh admin@xxx.xxx.xxx.xxx
      

   2. 以下のコマンドを実行して、ローカルUnixソケット接続方式でテナント mysql001 に接続します。

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

   3. エクスポートパスを /home/admin に設定します。

      obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
      Query OK, 0 rows affected
      

3. データベースに再接続した後、SELECT INTO OUTFILE ステートメントを使用してテーブル tbl1 内のデータをエクスポートします。エクスポートファイル名は tbl1.sql とします。2つのフィールド値間の区切りとしてカンマを指定します。文字列型の値は " 文字で囲みます。終了マークとして改行を使用します。

   obclient [test]> SELECT * INTO OUTFILE '/home/admin/tbl1.sql'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       FROM tbl1;
   Query OK, 3 rows affected
   

4. マシンにログインし、デバイスのローカル /home/admin ディレクトリでエクスポートされたファイル情報を確認します。

   [xxx@xxx /home/admin]# cat tbl1.sql
   1,"one",80
   2,"two",90
   3,"three",100
   

詳細を見る

SELECT INTO OUTFILE メソッドでエクスポートされたファイルは、LOAD DATA ステートメントを使用してインポートできます。詳細な方法については、LOAD DATAを使用したデータのインポートを参照してください。