データの挿入

テーブル作成後、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 select_stmt;

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の例

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
   

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
  

関連ドキュメント

• データの更新

• データの削除

• データの置換