Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row. The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 Mysql> insert into foo (u) values (10) on duplicate key update u = 20 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, The configuration variable innodb_autoinc_lock_mode=1 (the default): mysql> create table foo (id serial primary key, u int, unique key (u)) See demonstration below, tested with Percona Server 5.5.28. It's true that the new id is generated, but it is not used in the changed row. The edit tried to add a claim that INSERT.ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.Ī user tried to edit this post (the edit was rejected by moderators). Side effects are propagated to replicas too.Ĭorrection: both REPLACE and INSERT.ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL.Triggers that fire on DELETE are executed unnecessarily.Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |