MySQL REPLACE : insérer ou mettre à jour une ligne

La syntaxe MySQL REPLACE permet d'insérer une nouvelle ligne si la clé fournit n'existe pas ou de la mettre à jour sinon.

Insertion d'une ligne avec replace
mysql> REPLACE INTO test (id, name) VALUES (1, 'nico');
Query OK, 1 ROW affected (0.02 sec)

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

Mise à jour d'une ligne avec replace
mysql> REPLACE INTO test (id, name) VALUES (1, 'nico2');
Query OK, 2 ROWS affected (0.01 sec)

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

Vous remarquerez dans le dernier exemple que 2 lignes ont été modifiées. Contrairement à ce que l'on peut penser, REPLACE ne fait pas d'UPDATE : s'il y a un conflit sur la clé, il DELETE l'ancienne ligne avant d'INSERT la nouvelle, attention donc aux nombreux effets de bords (suppressions en cascade, déclenchement de triggers etc...) !

Pour en être sûr, nous allons créer des triggers pour l'UPDATE, le DELETE et l'INSERT sur la table "test". Ces triggers inséreront une ligne dans la table "trigger_test".

Création de triggers avec MySQL
DELIMITER $$
CREATE TRIGGER trigger_insert AFTER INSERT ON test
FOR EACH ROW
  BEGIN
    INSERT INTO trigger_test(action) VALUES('insert');
  END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trigger_update AFTER UPDATE ON test
FOR EACH ROW
  BEGIN
    INSERT INTO trigger_test(action) VALUES('update');
  END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trigger_delete AFTER DELETE ON test
FOR EACH ROW
  BEGIN
    INSERT INTO trigger_test(action) VALUES('delete');
  END $$
DELIMITER ;

Après avoir rejoué les deux tests précédents, voici le contenu de la table "trigger_test" :

REPLACE fait un DELETE puis un INSERT
mysql> SELECT * FROM trigger_test;
+--------+
| action |
+--------+
| INSERT | -- test 1
| DELETE | -- test 2
| INSERT |
+--------+
3 ROWS IN SET (0.01 sec)

Le REPLACE n'est pas limité aux clés primaires, il fonctionne avec toute contrainte d'unicité. Si une violation de contrainte d'unicité est détectée et que la clé primaire est un auto-incrément, cette clé sera incrémentée.

REPLACE sur une contrainte UNIQUE
mysql> CREATE TABLE IF NOT EXISTS `test` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) NOT NULL,
  `last_visit` datetime 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, last_visit) VALUES('nico', NOW());
Query OK, 1 ROW affected (0.01 sec)


mysql> SELECT * FROM test;
+----+------+---------------------+
| id | name | last_visit          |
+----+------+---------------------+
|  1 | nico | 2014-05-30 14:06:05 |
+----+------+---------------------+
1 ROW IN SET (0.00 sec)


mysql> REPLACE INTO test(name, last_visit) VALUES('nico', NOW());
Query OK, 2 ROWS affected (0.01 sec)


mysql> SELECT * FROM test;
+----+------+---------------------+
| id | name | last_visit          |
+----+------+---------------------+
|  2 | nico | 2014-05-30 14:08:07 |
+----+------+---------------------+
1 ROW IN SET (0.01 sec)