データの挿入

テーブルの作成後、INSERT ステートメントまたはその他のステートメントを使用して、テーブルにレコードを挿入できます。本記事では、関連するステートメントの使い方と例を紹介します。

データ挿入の準備

データを挿入する前に、以下の項目を確認してください：

• データベースのMySQLテナントに接続していることを確認してください。データベースへの接続操作については、接続方法の概要を参照してください。

  説明

  現在ログインしているテナントが属するテナントモードは、sys テナントで oceanbase.DBA_OB_TENANTS ビューをクエリすることで確認できます。

• 操作対象のテーブルの INSERT 権限を持っていることを確認してください。現在のユーザー権限を確認する操作の詳細については、ユーザー権限の確認を参照してください。この権限を持っていない場合は、管理者に連絡し権限の付与を依頼してください。ユーザー権限に関する操作については、直接権限付与を参照してください。

INSERT INTOステートメントを使用したデータの挿入

INSERT ステートメントを使用し、下記の提案を参考にしてテーブルにデータを挿入してください。

INSERT INTO ステートメントの構文は以下のとおりです：

INSERT INTO table_name [(list_of_columns)] VALUES (list_of_values);

データの挿入に関する推奨事項

• データを挿入する前に、列のタイプ、有効な値、NULLを許容するかどうかなど、テーブルのすべての列情報について確認することを推奨します。

  列情報は DESC ステートメントで確認できます。

  obclient [test]> DESC test;
  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | col1  | int(11) | NO   |     | NULL    |       |
  | col2  | int(11) | YES  |     | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  2 rows in set
  

  • 列属性が NOT NULL の場合

    • 列属性にデフォルト値が設定されている場合、挿入時にその列の値を指定しなくても、システムがその列にデフォルト値を挿入します。

    • 列属性にデフォルト値が設定されていない場合、挿入時にはその列の値を指定する必要があります。

  • 列属性が NULL の場合、挿入時にその列の値を指定しなくても、システムがその列に NULL 値を挿入します。

• データを挿入する前に、テーブルの列の制約定義を確認し、データ挿入時のエラーを避けることを推奨します。

  NOT NULL、PRIMARY KEY 制約、UNIQUE 制約はすべて DESC ステートメントで確認することができます。FOREIGN KEY、CHECK 制約は、information_schema.TABLE_CONSTRAINTS ビューをクエリすることで確認することができます。

単一行データの挿入

INSERT ステートメントを使用することで、単一行のデータを挿入できます。複数のレコードを挿入する必要がある場合は、複数の単一行挿入ステートメントを実行することで対応できます。一括挿入が必要な場合は、複数行データの一括挿入 を参照して操作することができます。

データを挿入するテーブルの想定情報は以下のとおりです：

obclient [test]> CREATE TABLE t_insert(
    id int NOT NULL PRIMARY KEY,
    name varchar(10) NOT NULL, 
    value int,
    gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
Query OK, 0 rows affected 

この中で、テーブルの id 列と name 列は空欄にすることができず、id 列は主キーであるため、一意性制約の要件を満たす必要があり、重複する値を持つことはできません。gmt_create 列にはデフォルト値が設定されています。

例1：複数の単一行挿入ステートメントを使用して、複数行のデータを挿入します。

gmt_create 列にはデフォルト値が指定されているため、データ挿入時にデフォルト値を指定する必要はありません。

obclient [test]> INSERT INTO t_insert(id, name, value) 
VALUES (1,'CN',10001);
Query OK, 2 rows affected

obclient [test]> INSERT INTO t_insert(id, name, value) 
VALUES(2,'US', 10002);
Query OK, 2 rows affected

注意：gmt_create 列にデフォルト値が指定されていない場合、データ挿入時に値を指定し、以下のステートメントを使用する必要があります。

obclient [test]> INSERT INTO t_insert(id, name, value, gmt_create)
 VALUES (3,'EN', 10003, current_timestamp ());
Query OK, 1 row affected 

複数行データの一括挿入

データ挿入時に、複数のレコードを挿入する必要がある場合は、1つの INSERT ステートメントに複数の VALUES を含めて、一括挿入を行うこともできます。単一の複数行挿入ステートメントは、複数の単一行挿入ステートメントよりも高速です。

例1の操作は、以下のステートメントでも実行できます。

例2：複数行データを一括挿入します。

obclient [test]> INSERT INTO t_insert(id, name, value) 
  VALUES (1,'CN',10001),(2,'US', 10002);
Query OK, 2 rows affected

さらに、テーブルデータのバックアップが必要な場合、またはテーブルのすべてのレコードを別のテーブルにコピーする必要がある場合、INSERT INTO ... SELECT ... FROM クエリを使用して、INSERT の values 句として一括挿入を実行できます。

例3：テーブル t_insert の全データを t_insert_bak テーブルにバックアップします。

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 15:17:17 |
|  2 | US   | 10002 | 2022-10-12 16:29:16 |
|  3 | EN   | 10003 | 2022-10-12 16:29:26 |
+----+------+-------+---------------------+
3 rows in set

obclient [test]> CREATE TABLE t_insert_bak(
    id number NOT NULL PRIMARY KEY,
    name varchar(10) NOT NULL, 
    value number,
    gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
Query OK, 0 rows affected 

obclient [test]> INSERT INTO t_insert_bak SELECT * FROM t_insert;
Query OK, 2 rows affected

obclient [test]> SELECT * FROM t_insert_bak;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 15:17:17 |
|  2 | US   | 10002 | 2022-10-12 16:29:16 |
|  3 | EN   | 10003 | 2022-10-12 16:29:26 |
+----+------+-------+---------------------+
3 rows in set

一意性制約違反の回避

テーブルに一意性制約が設定されている場合、同じレコードを挿入しようとすると、データベースはエラーを報告します。エラーメッセージは以下のとおりです：

obclient [test]> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

このエラーは、INSERT IGNORE INTO ステートメントまたは INSERT INTO ON DUPLICATE KEY UPDATE ステートメントを用いることで回避できます。

例：

• INSERT IGNORE INTO ステートメントで制約違反を回避する場合、IGNORE キーワードは制約違反によって INSERT が失敗した際の影響を無視させることができます。

obclient [test]> INSERT IGNORE INTO t_insert(id, name, value) 
    VALUES (3,'UK', 10003),(4, 'JP', 10004);
Query OK, 1 row affected 

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 15:17:17 |
|  2 | US   | 10002 | 2022-10-12 16:29:16 |
|  3 | EN   | 10003 | 2022-10-12 16:29:26 |
|  4 | JP   | 10004 | 2022-10-12 17:02:52 |
+----+------+-------+---------------------+
4 rows in set

例では、INSERT IGNORE INTO ステートメントが使用されています。(3,'UK', 10003) 行のデータ挿入に失敗しましたが、システムはエラーを報告しませんでした。

• INSERT INTO ON DUPLICATE KEY UPDATE ステートメントで制約違反を回避する場合、重複する主キーまたは一意キーに対する後続の処理を指定することができます。

  説明

  • ON DUPLICATE KEY UPDATE column_name = expr を指定する場合：挿入しようとする主キーまたは一意キーが重複する場合、代入ステートメント column_name = expr を用いてテーブル内で競合する行のデータを更新することができます。 代入ステートメント column_name = expr を用いて、競合する行に特定の1列または複数の列の値を割り当てることができます。複数の列の値を割り当てる場合、列と列の間をカンマ（,）で区切ります。
  • ON DUPLICATE KEY UPDATE column_name = expr を指定しない場合：挿入しようとする主キーまたは一意キーが重複する場合、データの挿入時にシステムはエラーを報告します。

  obclient [test]> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(5, 'CN', 10005) ON DUPLICATE KEY UPDATE name = VALUES(name);
  Query OK, 1 row affected 
  
  obclient [test]> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10001 | 2022-10-12 16:29:16 |
  |  2 | US   | 10002 | 2022-10-12 15:17:17 |
  |  3 | UK   | 10003 | 2022-10-12 16:29:26 |
  |  4 | JP   | 10004 | 2022-10-12 17:02:52 |
  |  5 | CN   | 10005 | 2022-10-12 17:27:46 |
  +----+------+-------+---------------------+
  5 rows in set
  

  例中の ON DUPLICATE KEY UPDATE name = VALUES(name) は、挿入するデータがテーブルの主キー値と重複する場合、テーブルの競合する行の元のデータ (3,'EN', 10003) の name 列の値を、現在挿入しようとしている name 列のデータに更新することを意味します。他の競合しない行は、正常に挿入されます。

INSERT OVERWRITE SELECTステートメントを使用してデータを挿入する

INSERT OVERWRITE SELECT ステートメントは、クエリの結果でテーブルまたはパーティション内の既存データを置き換え、クエリで取得したデータをターゲットテーブルまたはパーティションに上書きすることができます。

このステートメントの構文は以下のとおりです：

INSERT [/*+PARALLEL(N)*/] OVERWRITE table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt;

INSERT OVERWRITE SELECTの使用制限

テーブルレベルのINSERT OVERWRITE SELECTの使用制限

• このステートメントは、複数行トランザクションでは操作できません。そのため、操作をスムーズに実行するために、まず SET autocommit = on; コマンドを実行して自動トランザクションコミットモードを有効にする必要があります。
• 書き込み対象のテーブルにテーブルロックがかかります。同じテーブルに対してDDL操作を同時に実行することは許可されず、同時に実行されたDML操作はテーブルロックが解放されるまでタイムアウトするまで待機します。操作中にテーブルに対してクエリを実行することは可能です。
• このステートメントが操作するソースデータと、ターゲットテーブルで上書きされる列の数は、厳密に一致している必要があります。一致しない場合はエラーが発生します。
• このステートメントのデータ書き込み操作は、フル・ダイレクトロード方式を採用しているため、操作はフル・ダイレクトロード機能の制限を受けます。ダイレクトロードに関する情報は、INSERT INTO SELECTステートメントによるデータのダイレクトロード の 使用上の制限 セクションを参照してください。
• このステートメントは、ダイレクトロードのヒントを指定するとエラーが発生します。
• PDML（Parallel Data Manipulation Language、並列データ操作言語）フレームワークの制限により、PDMLでサポートされていないシナリオではデータのインポートができません。INSERT OVERWRITE SELECT は、「not supported」エラーを報告をします。並列DMLの詳細については、並列DMLを参照してください。

パーティションレベルのINSERT OVERWRITE SELECT

機能紹介

• ソーステーブルはパーティションテーブルでも非パーティションテーブルでも構いません。パーティショニングタイプに制限はありません。
• ターゲットテーブルは、パーティションまたはサブパーティションをサポートします。
• ターゲットテーブルはローカルインデックスとLOBをサポートしています。
• ターゲットテーブルのすべてまたは一部のパーティションを指定できます。ターゲットテーブルにサブパーティションがある場合は、パーティションとサブパーティションを個別または混合して指定できます。
• 指定されたターゲットテーブルのパーティションにソーステーブルからのデータがヒットしない場合、そのパーティションのデータはクリアされます（空データで上書きされます）。
• OceanBaseデータベースV4.3.5 BP1バージョンから、パーティションレベルの insert overwrite 操作は、ターゲットテーブルの最後のレベルのパーティションがHashまたはKeyのパーティションであることをサポートします。

使用制限

• 指定されたパーティションは既に存在している必要があります。そうでない場合、エラーが発生します。
• フルダイレクトロードに基づいており、/*+ append */ ヒントを追加する必要はありません。そうしないと、使用時にエラーが発生します。
• このステートメントは、複数行トランザクションでは操作できません。そのため、操作をスムーズに実行するために、まず SET autocommit = on; コマンドを実行して自動トランザクションコミットモードを有効にする必要があります。
• ソーステーブルからのデータがターゲットテーブルのパーティションルールに従って計算された後、指定されたターゲットテーブルのパーティションにヒットしない場合、パーティションが存在しないというエラーが発生します。
• 現在、ターゲットテーブルに自動インクリメント列はサポートされていません。
• 現在、ターゲットテーブルにグローバルインデックスまたは外部キーはサポートされていません。
• 現在、ターゲットテーブルとして外部テーブルはサポートされていません。

INSERT OVERWRITE SELECTの例

例1

1. テストテーブルを2つ作成します：source_tbl1 をデータソースとし、target_tbl1 をターゲットテーブルとします。

   CREATE TABLE source_tbl1 (col1 INT, col2 VARCHAR(20), col3 INT);
   

   CREATE TABLE target_tbl1 (col1 INT, col2 VARCHAR(20), col3 INT);
   

2. source_tbl1 テーブルにサンプルデータを追加します。

   INSERT INTO source_tbl1 VALUES (1, 'A1', 30),(2, 'B2', 25),(3, 'C3', 22);
   

3. target_tbl1 テーブルにサンプルデータを追加します。

   INSERT INTO target_tbl1 VALUES (4, 'D4', 35),(5, 'E5', 28);
   

4. target_tbl1 テーブルからデータをクリエします。

   SELECT * FROM target_tbl1;
   

   実行結果は次のとおりです：

   +------+------+------+
   | col1 | col2 | col3 |
   +------+------+------+
   |    4 | D4   |   35 |
   |    5 | E5   |   28 |
   +------+------+------+
   2 rows in set
   

5. INSERT OVERWRITE SELECT ステートメントを使用して、col3 が25より大きいデータを source_tbl1 から選択し、それらのデータを target_tbl1 に挿入して、既存の内容を置き換えます。

   INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;
   

6. target_tbl1 テーブルの置き換え後のデータを確認します。

   SELECT * FROM target_tbl1;
   

   実行結果は次のとおりです：

   +------+------+------+
   | col1 | col2 | col3 |
   +------+------+------+
   |    1 | A1   |   30 |
   +------+------+------+
   1 row in set
   

例2

INSTER OVERWRITE PARTITION SELECT の使用例：

1. パーティションテーブル t1_ins_overwrt を作成します。

   obclient> CREATE TABLE t1_ins_overwrt (c1 int primary key, c2 int) partition by range(c1)(
     partition p0 values less than(10),
     partition p1 values less than(20),
     partition p2 values less than(30),
     partition p3 values less than (MAXVALUE)
   );
   Query OK, 1 row affected
   

2. パーティションテーブル t2_ins_overwrt を作成します。

   obclient> CREATE TABLE t2_ins_overwrt (c1 int primary key, c2 int) partition by range(c1)(
     partition p0 values less than(10),
     partition p1 values less than(20),
     partition p2 values less than(30),
     partition p3 values less than (MAXVALUE)
   );
   Query OK, 1 row affected
   

3. それぞれのテーブルに数件のデータを挿入します。

   obclient> INSERT INTO t1_ins_overwrt values (1, 10), (12, 20), (23, 30), (34, 40), (45, 50);
   Query OK, 5 rows affected (0.018 sec)
   Records: 5  Duplicates: 0  Warnings: 0
   
   obclient> INSERT INTO t2_ins_overwrt values (3, 60), (8, 80), (17, 170), (26, 260), (29, 290), (142, 1420);
   Query OK, 6 rows affected (0.015 sec)
   Records: 6  Duplicates: 0  Warnings: 0
   

4. それぞれのテーブルからp3パーティションのデータをクエリします。

   obclient> SELECT * FROM t1_ins_overwrt partition(p3);
   +------+------+
   | c1   | c2   |
   +------+------+
   |   34 |   40 |
   |   45 |   50 |
   +------+------+
   2 rows in set (0.006 sec)
   
   obclient> SELECT * FROM t2_ins_overwrt partition(p3);
   +------+------+
   | c1   | c2   |
   +------+------+
   |  142 | 1420 |
   +------+------+
   1 row in set (0.006 sec)
   

5. 次のSQLクエリを実行して t1_ins_overwrt テーブルのp3パーティションのデータを t2_ins_overwrt テーブルのp3パーティションに挿入します。

   obclient> INSERT OVERWRITE t2_ins_overwrt partition(p3) select * from t1_ins_overwrt partition(p3);
   Query OK, 2 rows affected (1.354 sec)
   Records: 2  Duplicates: 0  Warnings: 0
   

6. t2_ins_overwrt テーブルのp3パーティションをクエリしたところ、データがすでに上書きされていることが確認できました。

   obclient> SELECT * FROM t2_ins_overwrt partition(p3);
   +------+------+
   | c1   | c2   |
   +------+------+
   |   34 |   40 |
   |   45 |   50 |
   +------+------+
   2 rows in set (0.006 sec)
   

例3

1. パーティションテーブル tbl1 を作成します。

      obclient [test]> CREATE TABLE tbl1(col1 INT,col2 INT) 
                     PARTITION BY RANGE COLUMNS(col1) 
                        SUBPARTITION BY HASH(col2) SUBPARTITIONS 3 (
                           PARTITION p0 VALUES LESS THAN(10),                                               
                           PARTITION p1 VALUES LESS THAN(20));
    Query OK, 0 rows affected
   

2. tbl1 テーブルに1件のデータを挿入します。

   obclient [test]> INSERT INTO tbl1 (col1, col2) VALUES (2, 3);
                    INSERT INTO tbl1 (col1, col2) VALUES (15, 2);
   

3. テーブル tbl1 のパーティションp0にデータが挿入されているかをクエリします。

   obclient [test]> SELECT * FROM tbl1 PARTITION (p0);
   

   クエリ結果は以下のとおりです：

   +------+------+
   | col1 | col2 |
   +------+------+
   |    2 |    3 |
   +------+------+
   

   obclient [test]> SELECT * FROM tbl1 PARTITION (p1);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | col1 | col2 |
    +------+------+
    |   15 |    2 |
    +------+------+
   

4. パーティションテーブル tbl2 を作成します。

   obclient [test]> CREATE TABLE tbl2(col1 INT,col2 INT) 
                     PARTITION BY RANGE COLUMNS(col1) 
                        SUBPARTITION BY HASH(col2) SUBPARTITIONS 3 (
                           PARTITION p0 VALUES LESS THAN(10),                                               
                           PARTITION p1 VALUES LESS THAN(20));
    Query OK, 0 rows affected
   

5. テーブル tbl2 にデータを挿入します。

   obclient [test]> INSERT INTO tbl2 (col1, col2) VALUES (0, 22);
                    INSERT INTO tbl2 (col1, col2) VALUES (18, 6);
   

6. テーブル tbl2 の p0 と p1 パーティションにデータが挿入されているかをクエリします。

   obclient [test]> SELECT * FROM tbl2 PARTITION (p0);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | c1   | c2   |
    +------+------+
    |    0 |   22 |
    +------+------+
   

   obclient [test]> SELECT * FROM tbl2 PARTITION (p1);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | c1   | c2   |
    +------+------+
    |    18 |   6 |
    +------+------+
   

7. insert overwrite を使用して、テーブル tbl1 のデータをテーブル tbl2 の指定されたパーティション p1 に上書き書き込みます。

   obclient [test]> insert overwrite tbl2 partition (p1) select * from tbl1 partition (p1);
   

8. テーブル tbl2 のパーティションp1のデータが上書きされているか検証します。

   obclient [test]> SELECT * FROM tbl2 PARTITION (p1);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | col1 | col2 |
    +------+------+
    |   15 |    2 |
    +------+------+
   

   結果は、テーブル tbl2 のパーティションp1のデータが上書きされたことを示しています。

REPLACE INTOステートメントを使用してデータを挿入する

INSERT ステートメント以外にも、テーブルにデータレコードが存在しない場合、またはテーブルにデータレコードが存在するが、主キーまたは一意キーとの競合がない場合、REPLACE INTO ステートメントを INSERT ステートメントの代わりに使用してデータを挿入することができます。REPLACE INTO ステートメントの構文の詳細と説明については、REPLACEを参照してください。

例：

• テーブル t_replace の作成後、REPLACE INTO ステートメントを使用してデータを挿入します。

  obclient [test]> CREATE TABLE t_replace(
      id int NOT NULL PRIMARY KEY
      , name varchar(10) NOT NULL
      , value int
      ,gmt_create timestamp NOT NULL DEFAULT current_timestamp
  );
  Query OK, 0 rows affected 
  
  obclient [test]> REPLACE INTO t_replace VALUES(1,'CN',2001, current_timestamp ());
  Query OK, 1 row affected 
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-11-23 09:52:44 |
  +----+------+-------+---------------------+
  1 row in set 
  

• データレコードがあるテーブル t_replace に、REPLACE INTO ステートメントを使用してデータを挿入します。

  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-03-22 16:13:55 |
  +----+------+-------+---------------------+
  1 row in set 
  
  obclient [test]> REPLACE INTO t_replace values(2,'US',2002, current_timestamp ());
  Query OK, 1 row affected
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-11-23 09:52:44 |
  |  2 | US   |  2002 | 2022-11-23 09:53:05 |
  +----+------+-------+---------------------+
  2 rows in set
  

関連ドキュメント

• データの更新

• データの削除

• データの置換