Amazing! Anyway the way I solved it was by using 2 queries. When I first wrote my DB query code, it did so many separate queries that it took 5 hours. In terms of previouslly discussed "overheads". I had the same frustration of gaps in the auto increment but I found a way to avoid it. A better solution is to calculate incremental values on output. Essentially, you need to put a lock on the entire table, and renumber everything that needs to be renumbered, typically using a trigger. If that is a requirement, the overhead on the updates and inserts is much larger. You should not depend on auto_increment having no gaps. Then the duplicate is detected and the update happens. This is when the id gets auto incremented. With anĪuto-increment column, an INSERT statement increases theĪuto-increment value but UPDATE does not.) Statements have similar effect: INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1 Īn InnoDB table where a is an auto-increment column. For example, if column a isĭeclared as UNIQUE and contains the value 1, the following two Would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that This behavior is documented (paragraph in parentheses):
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |