On Tue, 31 Oct 2023 11:21:54 +1100 Richard Ellerbrock via discuss <[email protected]> wrote:
> it appears that when using INSERT ON DUPLICATE > UPDATE with a primary auto_increment column the auto_increment is > increased even if the INSERT fails and the UPDATE occurs. > Is this correct behaviour? This seems to be the expected behaviour. See, e.g. <https://mariadb.com/kb/en/auto_increment-on-insert-on-duplicate-key-update/>: >> It[']s part of the architecture that it[']s easier to allocate an >> auto_increment value before it detected that a duplicate occurs The AUTO_INCREMENT FAQ doesn't explicitly state that values are allocated before checking validity (and then potentially discarded), but it does mention that gaps in the numbers are to be expected: >> How do I renumber rows once I've deleted some in the middle? >> Typically, you don't want to. Gaps are hardly ever a problem; if >> your application can't handle gaps in the sequence, you probably >> should rethink your application. -- <https://mariadb.com/kb/en/auto_increment-faq/> There is advice on the Internet that changing the AUTO_INCREMENT locking mode in InnoDB may give you different behaviour <https://mariadb.com/kb/en/auto_increment-handling-in-innodb/>, but that change also comes with side effects that might be undesirable. -- Best regards, Ivan _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
