Matthew Scharley Matthew Scharley - 6 months ago 21
SQL Question

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?

Answer

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.