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

Comment insérer une nouvelle ligne ou la mettre à jour si elle existe déjà avec MySQL ?

30 mai 2014

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)

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 ON DUPLICATE KEY UPDATE : insérer ou mettre à jour une ligne
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
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)