Sequences in MySQL

In Oracle sequences are often used to maintain a unique series of numbers for an ID field. Sequences are independant of any table and can be used to keep a value unique across a number of tables. In fact they are not even restricted to use in tables.

MySQL doesn't currently support sequences. However it does have an auto increment value which can be applied to a primary key of a table. This is done during the table creation.

mysql> create table seq_test
      (id int primary key auto_increment
      ,name varchar(30));
Query OK, 0 rows affected (0.06 sec)

As the name suggests the value is assigned automatically, this is in contrast to Oracle where we have to call the nextval function of the sequence to return the value when we need it.

So if we perform an insert against the table but do not specifiy a value for the auto_increment column it's assigned automatically.

mysql> insert into seq_test (name) values ('Dave');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+----+------+
| id | name |
+----+------+
|  1 | Dave |
+----+------+
1 row in set (0.03 sec)

However we can override the assignment using a value of our choosing.

mysql> insert into seq_test (id,name) values (100,'John');
Query OK, 1 row affected (0.05 sec)

mysql> select * from seq_test;
+-----+------+
| id  | name |
+-----+------+
|   1 | Dave |
| 100 | John |
+-----+------+
2 rows in set (0.00 sec)

The sequence will then start from this new higher point.

mysql> insert into seq_test (name) values ('Penny');
Query OK, 1 row affected (0.02 sec)

mysql> select * from seq_test;
+-----+-------+
| id  | name  |
+-----+-------+
|   1 | Dave  |
| 100 | John  |
| 101 | Penny |
+-----+-------+
3 rows in set (0.00 sec)

We can insert records with values lower than the current highest value but these will be subject to the normal rules for a primary key (no duplicates) and will not effect the next number assigned automatically.

If all of the records are deleted from a table the sequence is not reset.

mysql> delete from seq_test;
Query OK, 3 rows affected (0.01 sec)

mysql> insert into seq_test (name) values ('garry');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+-----+-------+
| id  | name  |
+-----+-------+
| 103 | garry |
+-----+-------+
1 row in set (0.02 sec)

To do this you need to truncate the table like so.

mysql> truncate table seq_test;
Query OK, 1 row affected (0.03 sec)

mysql> insert into seq_test (name) values ('garry');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+----+-------+
| id | name  |
+----+-------+
|  1 | garry |
+----+-------+
1 row in set (0.00 sec)

Alternatively you can reset the sequence using an alter table command.

 
mysql> alter table seq_test auto_increment = 100;
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

This method could also be used to assign a higher number to start the sequence rather than starting with 1 by calling the alter table straight after the table creation.

So in summary auto_increment offers a great way of assigning a unique value automatically to a table. However what it doesn't allow when compared with an Oracle Sequence is different increment values, ability to use across a number of tables and the option to assign in a reverse order.