データの挿入

テーブル作成後、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 列はNULLを含むことができず、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ステートメントを使用したデータのダイレクトロード の制限セクションを参照してください。
• このステートメントでダイレクトロードHintを指定すると、エラーが発生します。
• PDML（Parallel Data Manipulation Language、並列データ操作言語）フレームワークの制限により、PDMLがサポートしないシナリオではデータをインポートできず、INSERT OVERWRITE SELECT でnot supportedエラーが発生します。並列DMLの詳細については、並列DML を参照してください。

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

機能紹介

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

制限

• 指定したパーティションは既に存在している必要があります。存在しない場合はエラーが発生します。
• フルダイレクトロードに基づいているため、/*+ append */ Hintを追加する必要はありません。追加するとエラーが発生します。
• このステートメントは、複数行トランザクション内では実行できません。操作をスムーズに進めるためには、事前に 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

INSERT 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
  

関連ドキュメント

• データの更新
• データの削除
• データの置換