|
CREATE TABLE `t1` (
|
`c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`c2` bigint(20) unsigned NOT NULL,
|
`c3` bigint(20) unsigned NOT NULL,
|
`c4` varchar(128) DEFAULT NULL,
|
PRIMARY KEY (`c1`),
|
UNIQUE KEY `uq` (`c2`,`c3`),
|
KEY `c3` (`c3`),
|
KEY `c4` (`c4`)
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
|
|
mysql> set session optimizer_switch='extended_keys=off';
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> set session optimizer_switch='extended_keys=on';
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
|
ERROR 1032 (HY000): Can't find record in 't1'
|
mysql> truncate table t1;
|
Query OK, 0 rows affected (0.19 sec)
|
|
mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t1 (c2, c3, c4) values (58291525, 2580, 'foobar') on duplicate key update c4 = values(c4);
|
ERROR 1032 (HY000): Can't find record in 't1'
|
If the duplicate key exists in the table, extended_keys prevents updating of the data. If there is no duplicate, extended_keys allows insertion but subsequent updating via the "on duplicate key" update mechanism is prevented.
|