Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Long story short, I have a SQL file that I want to import as a skel style file, so this will be done repeatedly, programmatically. I can edit the SQL file however I want, but I'd rather not touch the application itself.

This application uses userid = 0 to represent the anonymous user. It also has a relevant (blank) entry in the database to represent this 'user'. Hence, the line in my skel.sql looks something like this:

INSERT INTO `{{TABLE_PREFIX}}users` VALUES (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, '', '', '', NULL);

The problem with this is that uid is a auto_increment field, for which, technically, 0 is an invalid value. Or atleast, if you set it to 0, you're basically telling MySQL, "Please insert the next id into this field."

Now, I suppose I could put an INSERT then an UPDATE query into my SQL file, but is there a way of telling MySQL in general that yes, I actually want to insert 0 into this field?

share|improve this question
up vote 8 down vote accepted

You can use:


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.

share|improve this answer

Check your sql DB mode with:


If it's empty or not set, use:


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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.