How to force MySQL to take 0 as a valid auto-increment value
Solution 1:
You can use:
SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'
Which as described here, will prevent MySQL from interpreting an INSERT/UPDATE ID of 0 as being the next sequence ID. Such behaviour will be limited to NULL.
It is what I'd consider pretty bad behaviour from the application though. You'll have to be real careful that it's used consistently, especially if you choose to implement replication at a later date.
Solution 2:
Check your sql DB mode with:
SELECT @@[GLOBAL|SESSION].sql_mode;
If it's empty or not set, use:
SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'
BE CAREFUL! If you use GLOBAL
, it's not an inmediate change, you need to restart your connection to apply the setting.
So, if you're restoring data from one DB to another, for example, and you're not sure if this setting is applied, use SESSION
for an inmediate change (resets when closing connection). When done, insert 0 value and it won't change even if the sql_mode
is changed.
To reset this mode (and others) use
SET [GLOBAL|SESSION] sql_mode=''
Zero autoincrement values are not recommended to be used, 'cause it's not set as default in mysql databases.
For more info check mysql dev page topic on this