MySQL ON DUPLICATE KEY UPDATE : insérer ou mettre à jour une ligne

Gérer les insertions de doublons avec MYSQL.

09 novembre 2014

La syntaxe MySQL ON DUPLICATE KEY UPDATE permet de mettre à jour une ligne si une violation de contrainte d'unicité est détectée lors d'un INSERT.

Utilisation de ON DUPLICATE KEY UPDATE

mysql> CREATE TABLE IF NOT EXISTS `test` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) NOT NULL,
  `nb_visits` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Query OK, 0 ROWS affected (0.04 sec)

mysql> INSERT INTO test(name, nb_visits) VALUES('nico', 1)
        ON DUPLICATE KEY UPDATE nb_visits = nb_visits + 1;
Query OK, 1 ROW affected (0.01 sec)


mysql> SELECT * FROM test;
+----+------+-----------+
| id | name | nb_visits |
+----+------+-----------+
|  1 | nico |         1 |
+----+------+-----------+
1 ROW IN SET (0.01 sec)


mysql> INSERT INTO test(name, nb_visits) VALUES('nico', 1)
        ON DUPLICATE KEY UPDATE nb_visits = nb_visits + 1;
Query OK, 2 ROWS affected (0.01 sec)


mysql> SELECT * FROM test;
+----+------+-----------+
| id | name | nb_visits |
+----+------+-----------+
|  1 | nico |         2 |
+----+------+-----------+
1 ROW IN SET (0.00 sec)


mysql> SELECT AUTO_INCREMENT
        FROM information_schema.tables
        WHERE TABLE_NAME = 'test' AND table_schema = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|              3 |
+----------------+
1 ROW IN SET (0.01 sec)

Comme pour la syntaxe REPLACE, la valeur de l'auto-incrément de la table augmente. Toutefois, la ligne mise à jour garde sa clé primaire, évitant ainsi les problèmes de cohérences lors de son utilisation en tant que clé étrangère.

Habituellement, une violation de contrainte lors d'un INSERT entraine une erreur. L'utilisation de la syntaxe INSERT IGNORE permet de transformer ces erreurs en simples warnings. Malheureusement, INSERT IGNORE met sous le tapis divers types d'erreurs qui peuvent avoir de lourdes conséquences : l'insertion de NULL dans des colonnes NOT NULL par exemple. La syntaxe ON DUPLICATE KEY UPDATE permet aisément de répondre à cette problématique :

Gestion des doublons avec MySQL

mysql> INSERT INTO test(name, nb_visits) VALUES('nico', 1)
        ON DUPLICATE KEY UPDATE id = id;
Query OK, 0 ROWS affected (0.02 sec)

A bientôt !

Par
Créateur et administrateur.

Dans la même catégorie

MySQL: comment faire une requête sur la description d'une table
MySQL : order by selon la valeur des champs
MySQL : une table pivot dynamique
SQL : faire un select sur une liste de valeurs
MySQL : créer une table à partir d'un select
MySQL : copier une table
MySQL REPLACE : insérer ou mettre à jour une ligne
MSSQL : Trouver le message qui correspond à un code d'erreur
MySQL : Comment autoriser les connexions distantes ?
MySQL : Quand l'encodage veut votre peau !
mysqldump : Comment ne pas prendre en compte une table ?
Dump d'une table MySQL avec une requête
MySQL en ligne de commande
DELETE avec des jointures
Sauvegarder automatiquement une base de données MySQL

Commentaire(s)