Use INSERT ON DUPLICATE KEY UPDATE to insert or update data - AnalyticDB

INSERT ON DUPLICATE KEY UPDATE (couramment appelé upsert) insère une ligne dans une table. Si une ligne possédant la même clé primaire existe déjà, cette instruction met à jour la ligne existante au lieu d'insérer un doublon.

AnalyticDB for MySQL applique la logique suivante :

Condition	Action	Lignes affectées
Aucun conflit	Exécute `INSERT`	1
Conflit de clé primaire	Exécute `UPDATE` sur la ligne existante	1

Notes d'utilisation

La clause ON DUPLICATE KEY UPDATE prend uniquement en charge les affectations de valeurs littérales et les affectations VALUES(). Les expressions arithmétiques, conditionnelles et les fonctions ne sont pas prises en charge. Une erreur survient si la clause référence un type d'expression non pris en charge.
```
-- Supported: literal value assignment
ON DUPLICATE KEY UPDATE course_name = 'Intro to SQL'

-- Supported: VALUES() assignment
ON DUPLICATE KEY UPDATE course_name = VALUES(course_name)

-- Not supported: arithmetic expression (causes an error)
ON DUPLICATE KEY UPDATE count = count + 1
```
Pour les écritures volumineuses ou à haute fréquence (plus de 100 QPS), l'utilisation du CPU peut augmenter considérablement. Privilégiez REPLACE INTO pour les mises à jour par lot.

Syntaxe

INSERT INTO table_name[(column_name[, …])]
[VALUES]
[(value_list[, …])]
ON DUPLICATE KEY UPDATE
   c1 = v1,
   c2 = v2,
   ...;

Exemples

Les exemples ci-dessous utilisent la table student_course. Créez-la à l'aide de l'instruction suivante :

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);

Insérer une nouvelle ligne

L'instruction suivante insère une ligne. Comme aucune ligne avec user_id = 11056941 n'existe encore, AnalyticDB for MySQL effectue l'insertion.

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';

Interrogez la table pour confirmer l'insertion :

SELECT * FROM student_course;

Résultat attendu :

+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+
| 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   |
+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+

Mettre à jour une ligne existante

L'instruction suivante utilise user_id = 11056941, ce qui correspond à la ligne existante. AnalyticDB for MySQL met à jour uniquement les colonnes spécifiées dans la clause ON DUPLICATE KEY UPDATE (course_name et business_id). Toutes les autres colonnes restent inchangées.

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';

Interrogez à nouveau la table :

SELECT * FROM student_course;

Résultat attendu : id reste 277941 (inchangé) et course_name est mis à jour vers --66 :

+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+
| 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   |
+-------+----------+---------------------+---------------------+---------------------+-----------+-----------------------------------------+------------+

FAQ

Pourquoi l'erreur « insert on duplicate key update statement only support 'primitive value' and values() expr » s'affiche-t-elle ?

La clause ON DUPLICATE KEY UPDATE accepte uniquement les valeurs littérales ou les affectations VALUES(). Les expressions arithmétiques (comme count + 1), conditionnelles et les fonctions ne sont pas prises en charge.

Corrigez l'instruction en utilisant une affectation de valeur littérale ou une affectation VALUES() :

Affectation de valeur littérale :

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';

Affectation 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);

Pourquoi l'erreur « Error: Field 'xxxx' doesn't have a default value » s'affiche-t-elle ?

La colonne est définie comme NOT NULL sans valeur par défaut, et l'instruction INSERT ne fournit pas de valeur pour celle-ci.

Appliquez l'une des solutions suivantes pour corriger le problème :

Ajoutez la valeur manquante de la colonne à l'instruction INSERT INTO ... ON DUPLICATE KEY UPDATE.
Modifiez la table pour ajouter une valeur par défaut à la colonne, puis réexécutez l'instruction.

Est-il possible d'insérer plusieurs lignes en une seule instruction ?

Oui. Fournissez plusieurs ensembles de valeurs dans la clause VALUES et gérez les conflits dans la clause ON DUPLICATE KEY UPDATE.

L'instruction suivante insère trois lignes. Pour toute ligne dont le user_id correspond à une ligne existante, seules les colonnes course_name et business_id sont mises à jour.

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);