Skip to content

AUTO_INCREMENT Trivia

Posted by Kristian Köhntopp on
MySQL (MyISAM) exhibits strange and little known (but documente) behaviour when dealing with compound keys where a part of the compound is AUTO\_INCREMENT.

This is as everybody knows:

mysql> create table standard ( name varchar(20), i integer not null auto_increment primary key );
Query OK, 0 rows affected (0.18 sec)

mysql> insert into standard ( name ) values ( "kris" ), ("azundris"), ("kris"), ("azundris");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from standard;
+----------+---+
| name | i |
+----------+---+
| kris | 1 |
| azundris | 2 |
| kris | 3 |
| azundris | 4 |
+----------+---+
4 rows in set (0.00 sec)

When you delete values at the end, they are not reused:

mysql> delete from standard where i in (3, 4);
Query OK, 2 rows affected (0.00 sec)

mysql> insert into standard ( name ) values ( "kris" ), ("azundris");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from standard;
+----------+---+
| name | i |
+----------+---+
| kris | 1 |
| azundris | 2 |
| azundris | 6 |
| kris | 5 |
+----------+---+
4 rows in set (0.00 sec)

But when you define this table as having a compound primary key (name, i), things are different.

mysql> create table compound ( name varchar(20), i integer not null auto_increment, primary key (name, i));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into compound ( name ) values ( "kris" ), ("azundris"), ("kris"), ("azundris");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from compound;
+----------+---+
| name | i |
+----------+---+
| azundris | 1 |
| azundris | 2 |
| kris | 1 |
| kris | 2 |
+----------+---+
4 rows in set (0.00 sec)

Also, counter values are suddenly reused:

mysql> delete from compound where i = 2;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into compound ( name ) values ( "kris" ), ("azundris"), ("kris"), ("azundris");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from compound;
+----------+---+
| name | i |
+----------+---+
| azundris | 1 |
| azundris | 2 |
| azundris | 3 |
| kris | 1 |
| kris | 2 |
| kris | 3 |
+----------+---+
6 rows in set (0.00 sec)

(As seen in the MySQL Certification Study Guide)

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

Bernd Eckenfels on :

Is there a use of a primary key which is a compound? It does not look like any uniqueness, and optimiting lookups with a index on the non-auto columns is most likely more selective, also.

Isotopp on :

"Is there a use of a primary key which is a compound?" Yes, there is, although it admittedly is a kludge.

If you have a setup with two MySQL servers in a cross replications setup, you'll run into problems with simple auto_increment.

The canonical workaround is to use a compound key (@@server_id, auto_increment_variable), which is guaranteed to be unique across all your servers connected by replication.

eckes on :

I prefer UUIDs (as I have written in my last comment) for everything :)

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

BBCode format allowed