テーブル作成後、INSERT ステートメントやその他のステートメントを使用して、テーブルに行レコードを挿入できます。本記事では、関連するステートメントの使い方と例を紹介します。
データ挿入の準備
データを挿入する前に、以下の点を確認してください：
データベースのMySQL互換モードのテナントに接続されていることを確認してください。データベースへの接続操作については、接続方法の概要をご参照ください。
説明
現在ログインしているテナントが属するテナントモードは、
sysテナントでoceanbase.DBA_OB_TENANTSビューをクエリすることで確認できます。操作対象のテーブルに対する
INSERT権限を持っていることを確認してください。現在のユーザー権限に関する操作については、テナントアカウント管理をご参照ください。
INSERT INTOステートメントを使用したデータの挿入
INSERT ステートメントを使用し、以下の点を参考にテーブルにデータを挿入してください。
INSERT INTO ステートメントの構文は以下のとおりです：
INSERT INTO table_name (list_of_columns) VALUES (list_of_values);
パラメータ |
必須 |
説明 |
|---|---|---|
| table_name | はい | データを挿入するテーブルを指定します |
| (list_of_columns) | いいえ | データを挿入する列を指定します |
| (list_of_values) | はい | list_of_columns で指定した列に対応する値です。値は列と1対1で対応している必要があります。 |
データ挿入の推奨事項
データを挿入する前に、列の型、有効な値、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;
パラメータ |
説明 |
|---|---|
| PARALLEL(N) | オプションです。上書き操作の並列実行レベルを指定します。指定しない場合、デフォルトで並列度2が採用されます。 |
| table_name | 挿入するテーブル名を指定します。 |
| select_stmt | SELECT 句を指定します。クエリステートメントの詳細については、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の例
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);テーブル
source_tbl1にサンプルデータを挿入します。INSERT INTO source_tbl1 VALUES (1, 'A1', 30),(2, 'B2', 25),(3, 'C3', 22);テーブル
target_tbl1にサンプルデータを挿入します。INSERT INTO target_tbl1 VALUES (4, 'D4', 35),(5, 'E5', 28);テーブル
target_tbl1のデータをクエリします。SELECT * FROM target_tbl1;実行結果は次のとおりです：
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 4 | D4 | 35 | | 5 | E5 | 28 | +------+------+------+ 2 rows in setINSERT OVERWRITE SELECTステートメントを使用し、col3が25より大きい条件でsource_tbl1からデータを選択し、それらのデータをtarget_tbl1に挿入して既存の内容を上書きします。INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;テーブル
target_tbl1のデータ置換後の内容を確認します。SELECT * FROM target_tbl1;実行結果は次のとおりです：
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | A1 | 30 | +------+------+------+ 1 row in set
REPLACE INTOステートメントを使用したデータの挿入
tINSERT ステートメント以外に、テーブルにデータレコードがない場合、またはテーブルにデータレコードはあるものの主キーまたは一意キーとの競合がない場合には、INSERT ステートメントの代わりに REPLACE INTO ステートメントを使用してデータを挿入することもできます。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