“INSERIR IGNORE” vs “INSERIR … A L’ACTUALITZACIÓ DE LA CLAU DUPLICADA”

Recomanaria fer servir INSERT...ON DUPLICATE KEY UPDATE.
si fas servir INSERT IGNORE llavors la fila no serà inserida si resulta en una clau duplicada. Però la declaració no generarà un error. En canvi, genera una advertència. Aquests casos inclouen:

• Inserir una clau duplicada en les columnes amb PRIMARY KEY o UNIQUE les restriccions.
• Inserir un NULL en una columna amb un NOT NULL la restricció.
• Insereix una fila a una taula de partició, però els valors que inserides no s’assignen a una partició.

Si fas servir REPLACE MySQL en realitat fa un DELETE seguit d’un INSERT internament, el que té alguns efectes secundaris inesperats:

• s’assigna una nova identificació de autoincrement.
• les files dependents amb claus externes poden ser eliminades (si s’usen claus externes en cascada) o bé evitar la REPLACE.
• Els gallets que disparen el foc sobre DELETE s’executen innecessàriament.
• els efectes secundaris es propaguen també als esclaus de la replicaci ó.

correcció: tots dos REPLACE i INSERT...ON DUPLICATE KEY UPDATE són invencions no estàndard, propietàries, específiques de MySQL. ANSI SQL 2003 defineix un MERGE que pot resoldre la mateixa necessitat (i més), però MySQL no suporta la MERGE declaració.

Un usuari va intentar editar aquest post (l’edició va ser rebutjada pels moderadors). L’edició va tractar d’afegir una afirmació que INSERT...ON DUPLICATE KEY UPDATE fa que s’assigni una nova identificació de autoincrement. És cert que el nou id és generada però no s’usa a la fila canviada.
Vegeu la demostració a continuació, provada amb el Servidor Percona 5.5.28. La variable de configuració innodb_autoinc_lock_mode=1 (el valor per defecte):

mysql> create table foo (id serial primary key, u int, unique key (u));mysql> insert into foo (u) values (10);mysql> select * from foo;+----+------+| id | u |+----+------+| 1 | 10 |+----+------+mysql> show create table foo\GCREATE TABLE `foo` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `u` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `u` (`u`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1mysql> insert into foo (u) values (10) on duplicate key update u = 20;mysql> select * from foo;+----+------+| id | u |+----+------+| 1 | 20 |+----+------+mysql> show create table foo\GCREATE TABLE `foo` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `u` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `u` (`u`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

L’anterior demostra que la declaració de l’IODKU detecta el duplicat, i invoca l’actualització per canviar el valor de u. Noti el AUTO_INCREMENT=3 indica que es va generar una identificació, però no es va utilitzar a la fila.
Mentre que REPLACE esborra la fila original i insereix una nova fila, generant i emmagatzemant una nova identificació de autoincrement:

mysql> select * from foo;+----+------+| id | u |+----+------+| 1 | 20 |+----+------+mysql> replace into foo (u) values (20);mysql> select * from foo;+----+------+| id | u |+----+------+| 3 | 20 |+----+------+