MySQL Sequence Usage

A MySQL sequence is a set of integers: 1, 2, 3, ..., and since a data table can only have one auto-increment primary key field, if you want to achieve automatic increment for other fields, you can use MySQL sequences to achieve this.

In this chapter, we will introduce how to use MySQL sequences.

Using AUTO_INCREMENT

The simplest way to use a sequence in MySQL is by using the MySQL AUTO_INCREMENT to define the sequence.

Example

The following example creates a table called insect, where the id field in the insect table can automatically increment without specifying a value.

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Retrieving AUTO_INCREMENT Value

In the MySQL client, you can use the LAST_INSERT_ID() function in SQL to retrieve the value of the auto-increment column from the last inserted record.

In PHP or PERL scripts, there are also corresponding functions to retrieve the value of the auto-increment column from the last inserted record.

PERL Example

Use the mysql_insertid attribute to get the value of AUTO_INCREMENT. Here is an example:

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP Example

PHP uses the mysql_insert_id() function to retrieve the value of the AUTO_INCREMENT column from the last executed insert SQL statement.

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Resetting the Sequence

If you delete multiple records from a table and want to reorder the remaining data's AUTO_INCREMENT column, you can achieve this by dropping the auto-increment column and then re-adding it. However, this operation should be done with caution, as adding new records while deleting old ones can lead to data confusion. Here is how you can do it:

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

Setting the Sequence Start Value

Normally, the sequence starts at 1, but if you need to specify a start value of 100, you can achieve this with the following statement:

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

Alternatively, you can also achieve this after the table has been created with the following statement:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;