SQL uses sequences

SQL uses sequences

A sequence is an ordered set of integers produced as needed: 1, 2, 3 ... Sequences are often used in databases because many applications require a unique value for each row in the data table, and sequences provide an easy way to do so.

This section explains how to use sequences in MySQL.

Use AUTO_INCREMENT column

The easiest way to use a sequence in MySQL is to define a column as AUTO_INCREMENT and leave the rest to MySQL:

Example

Try the following example, which creates a new table and then inserts a few records into it, adding records that do not need to specify the ID of the record because the value of the column is automatically increased by MySQL.

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)

Gets AUTO_INCREMENT value

LAST_INSERT_ID () is a SQL function that can be used where any SQL statement can be executed. In addition, Perl and PHP each provide their own unique functions for obtaining the value of the AUTO_INCREMENT record.

Perl example

Use mysql_insertid property to get the value of the AUTO_INCREMENT query. D epending on how the query is executed, the property can be accessed through a database handle or a statement handle. The following example is self-added through a database handle:

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

PHP example

After executing a query that produces self-added value, you can get this value by calling mysql_insert_id() to:

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

Number the existing sequence

When you delete a lot of records from a table, you might want to reorder all the records. This can be achieved with a little scathing, but be careful if there is a connection between your table and the other tables.

When you feel AUTO_INCREMENT have to reorder a column, remove it from the table and add it back. The following example shows how this approach can be used to reorder the ID values in the INSECT table:

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

A sequence from a specific value

By default, the sequence in MySQL starts at 1, but you can specify any other value when you create a data table. In the following example, MySQL set the starting value of the sequence to 100:

mysql> CREATE TABLE INSECT
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);

Alternatively, you can create a data table and then use ALTER TABLE to set the starting value of the sequence:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;