![]() ![]() ![]() 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: Inserting a row to a partitioned table, but the values you insert don't map to a partition.Inserting a NULL into a column with a NOT NULL constraint.Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.But the statement won't generate an error. If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. I would recommend using INSERT.ON DUPLICATE KEY UPDATE. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |