MySQL Forums
Forum List  »  Newbie

Re: Insert explicit value on auto_increment field
Posted by: Peter Brawley
Date: May 17, 2018 09:04AM

There is a way to defeat the behaviour you're tripping over ...

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

... but it's not good practice.

What problem are you trying to solve by inserting a row with id=0? Auto_increment values are not reliably sequentially, are generated to guarantee only uniqueness. Relying on specific auto_increment values or on their sequentiality is entirely unsound.

If you need sequentially, its easy to set up: use a table eg intseq(id int, used bool default false) and when you need one, take the lowest unused seqint, mark it used and insert it in the target table, all inside a transaction.

Options: ReplyQuote


Subject
Written By
Posted
Re: Insert explicit value on auto_increment field
May 17, 2018 09:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.