INSERT ON DUPLICATE KEY UPDATE 文を実行してテーブルのデータを更新すると、AnalyticDB for MySQL はまずテーブルにデータの行を挿入しようとします。新しい行に既存の行と競合するプライマリキーがある場合、同じプライマリキーを持つ既存の行が更新されます。
AnalyticDB for MySQL は、次のルールに基づいて SQL 文を選択します。
挿入する行が存在しない場合、AnalyticDB for MySQL は
INSERT文を使用して行を挿入します。影響を受ける行数は 1 です。挿入する行が存在する場合、AnalyticDB for MySQL は
ON DUPLICATE KEY UPDATE句を使用して既存の行を更新します。影響を受ける行数は 1 です。
使用方法
INSERT INTO ... ON DUPLICATE KEY UPDATE...文を実行して、値の割り当てを使用してデータを更新できますが、算術式や条件付きの式などの複雑な式は使用できません。INSERT ON DUPLICATE KEY UPDATE文を実行して大量のデータを更新したり、高頻度 (100 QPS 超) でデータを更新したりすると、CPU 使用率が大幅に増加する可能性があります。REPLACE INTO文を実行してデータをバッチで更新することをお勧めします。詳細については、「REPLACE INTO」をご参照ください。
構文
INSERT INTO table_name[(column_name[, ...])]
[VALUES]
[(value_list[, ...])]
ON DUPLICATE KEY UPDATE
c1 = v1,
c2 = v2,
...;例
この例では、student_course テーブルを使用します。次の文を実行して、student_course という名前のテーブルを作成します。
CREATE TABLE student_course(
id bigint,
user_id bigint,
nc_id varchar,
nc_user_id varchar,
nc_commodity_id varchar,
course_no varchar,
course_name varchar,
business_id varchar,
PRIMARY KEY(user_id)
) DISTRIBUTED BY HASH(user_id);次の文を実行して、student_course テーブルにデータ行を挿入します。
INSERT INTO student_course (`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--55',
business_id = 'kuaiji';SELECT * FROM student_course; 文を実行して、student_course テーブルのデータをクエリします。次のクエリ結果は、データの行が挿入されたことを示します。
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| id | user_id | nc_id | nc_user_id | nc_commodity_id | course_no | course_name |business_id |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
|277941 | 11056941 | 1001EE1000000043G2T5|1001EE1000000043G2TO | 1001A5100000003YABO2| kckm303 | Industrial Accounting Practice V9.0--55| kuaiji |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+次の文を実行して、student_course テーブルに別のデータの行を挿入します。
INSERT INTO student_course(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277942, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--66', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--66',
business_id = 'kuaiji';挿入する行は、最初に挿入された行と同じプライマリキー (user_id 列: 11056941) を使用します。上記の文が実行されると、ON DUPLICATE KEY UPDATE 句の course_name = 'Industrial Accounting Practice V9.0--66' and business_id = 'kuaiji' の値のみが更新されます。SELECT * FROM student_course; 文を実行して更新されたデータをクエリすると、次の結果が返されます。
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| id | user_id | nc_id | nc_user_id | nc_commodity_id | course_no | course_name |business_id |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
|277941 | 11056941 | 1001EE1000000043G2T5|1001EE1000000043G2TO | 1001A5100000003YABO2| kckm303 | Industrial Accounting Practice V9.0--66| kuaiji |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+よくある質問
`insert on duplicate key update` statement only support 'primitive value' and values() expr. エラーメッセージが返された場合はどうすればよいですか？
原因: INSERT INTO ... ON DUPLICATE KEY UPDATE... 文は、リテラル値の割り当て (例 1) と VALUES() の割り当て (例 2) をサポートしますが、複雑な式 (算術式や条件付きの式など) や関数はサポートしません。ON DUPLICATE KEY UPDATE 句が複雑な式や関数を参照すると、エラーが発生します。
解決策: ON DUPLICATE KEY UPDATE 句でリテラル値の割り当てまたは VALUES() の割り当てを使用して SQL 文を修正します。
例 1：リテラル値の割り当て
INSERT INTO student_course (`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--55',
business_id = 'kuaiji';例 2：VALUES（）割り当て
INSERT INTO student_course (`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = VALUES(course_name),
business_id = VALUES(business_id);エラーの解決方法: Field 'xxxx' doesn't have a default value?
原因: このエラーは、column_name column_datatype NOT NULL のように `NOT NULL` として定義され、デフォルト値がない列で発生します。このエラーは、この列に値を挿入せずに INSERT INTO ... ON DUPLICATE KEY UPDATE... 文を実行するとトリガーされます。
解決策: 次のいずれかのメソッドを使用します。
INSERT INTO ... ON DUPLICATE KEY UPDATE...文を修正して、列に値を挿入します。列のデフォルト値を変更します。その後、
INSERT INTO ... ON DUPLICATE KEY UPDATE...文を再度実行します。
INSERT ON DUPLICATE KEY UPDATE 文を実行してデータをバッチで挿入できますか？
はい、この文を実行してデータをバッチで挿入できます。INSERT ON DUPLICATE KEY UPDATE 文を実行してデータをバッチで挿入する場合、VALUES 句で複数の値のセットを指定し、ON DUPLICATE KEY UPDATE 句で競合の処理方法を指定できます。
たとえば、次の文を実行して、student_course テーブルに 3 行のデータを挿入します。
INSERT INTO student_course(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277943, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--77', 'kuaiji'),
(277944, 11056943, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--88', 'kuaiji'),
(277945, 11056944, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--99', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = VALUES(course_name),
business_id = VALUES(business_id);