How to force MySQL to take 0 as a valid auto-increment value

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?

 Answers

From the answer I got here:

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.

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.

---

If you do not want to mangle with mysql variables, here's a useful hack:

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

And then

UPDATE `{{TABLE_PREFIX}}users` SET id = 0 where id = -1;

Obviously, this assumes that you're not using negative values for your table ids.

---

How do I insert the key 0 into mysql for AUTO_INCREMENT row?

From http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

"No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers."

So it seems using 0 just tells it to auto_increment.

But if you are assigning ID numbers, I'm not sure why you have defined the column to use AUTO_INCREMENT?

---

You cannot force this to be zero. This is limited by auto_increment_offset server variable. The default value is 1, the range is [1 .. 65535].

More information: auto_increment_offset, How to set AUTO_INCREMENT step.

---

The basic solution is this:

Blockquote SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

See my comment for an additional issue I solved too with this being overridden mid script.