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

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)