CODEWITHSUNDEEP
mysqlauto-increment
Matthew Scharley
Matthew Scharley

Reputation: 132414

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?

Upvotes: 83

Views: 77374

Answers (4)

e18r
e18r

Reputation: 8191

If you do not want to deal 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 using a signed integer and not using negative values for your table ids.

Upvotes: 19

I.G. Pascual
I.G. Pascual

Reputation: 5995

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 immediate 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 immediate change (it resets when closing the 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 auto-increment values are not recommended because they're not set as default in MySQL databases.

For more info check mysql dev page topic on this

Update

For MariaDB use the command pointed out in this comment

SET sql_mode='NO_AUTO_VALUE_ON_ZERO'

Upvotes: 31

Matthew Scharley
Matthew Scharley

Reputation: 132414

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.

Upvotes: 103

Stefan Rogin
Stefan Rogin

Reputation: 1527

Fail safe way: 

SET @@session.sql_mode = 
    CASE WHEN @@session.sql_mode NOT LIKE '%NO_AUTO_VALUE_ON_ZERO%' 
        THEN CASE WHEN LENGTH(@@session.sql_mode)>0
            THEN CONCAT_WS(',',@@session.sql_mode,'NO_AUTO_VALUE_ON_ZERO')  -- added, wasn't empty
            ELSE 'NO_AUTO_VALUE_ON_ZERO'                                    -- replaced, was empty
        END
        ELSE @@session.sql_mode                                             -- unchanged, already had NO_AUTO_VALUE_ON_ZERO set
    END
  • Checks if NO_AUTO_VALUE_ON_ZERO already set in sql_mode
  • Adds to sql_mode if not empty
  • Sets only the session, I recommend using it only on the sessions where you need to insert a 0

Upvotes: 11

Related Questions