Xaprb

It is possible to explicitly insert values into an AUTO_INCREMENT in MySQL, but sometimes these inserts cause confusing behavior. In this article I’ll explain that behavior.

Background

Suppose I have the following table:

create table number (
    number int not null auto_increment primary key
) ;

Now suppose I insert some values into the primary key explicitly, bypassing the AUTO_INCREMENT feature:

insert into number(number) values (-100);
select * from number;
+--------+
| number |
+--------+
| -100   |
+--------+

So far, so good. Now, I’ll try to insert the value 0:

insert into number(number) values (0);
select * from number;
+--------+
| number |
+--------+
| -100   |
| 1      |
+--------+

Why is the value 1? It turns out that, by default, MySQL generates the next AUTO_INCREMENT value for the column when either NULL or 0 is inserted. The next value is not the maximum value plus one; it is the next value larger than an internal counter MySQL maintains, which starts at 0 by default.

This can be configured. It’s possible to set the NO_AUTO_VALUE_ON_ZERO so it allows explicitly inserted zero values, as follows:

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

The MySQL manual also has more detailed information on AUTO_INCREMENT behavior.

Technorati Tags:No Tags

You might also like:

1. Why IS NULL doesn’t always work in MySQL
2. What is a SQL blind insert?
3. SQL Server 2000 date and time puzzler
4. How to generate sequences and surrogate keys in generic SQL
5. How to implement a queue in SQL