MySQL Sequence Usage

MySQL sequence is a set of integers:1, 2, 3, ... , since a data table can only have one field as the auto-increment primary key, If you want to implement 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 to use MySQL AUTO_INCREMENT to define the sequence.

Online example

The following example creates the data table insect, The id in the insect table does not need to specify a value to achieve automatic growth.

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)

Get AUTO_INCREMENT Value

In the MySQL client, you can use The LAST_INSERT_ID() function in SQL is used to obtain the value of the auto-incrementing column of the last inserted table.

Corresponding functions are also provided in PHP or PERL scripts to obtain the value of the auto-incrementing column of the last inserted table.

PERL Example

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value. 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 obtain the value of the AUTO_INCREMENT column in the 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);

Reset sequence

If you have deleted multiple records from the data table and wish to rearrange the AUTO_INCREMENT column of the remaining data, you can achieve this by deleting the auto-incrementing column and then re-adding it. However, this operation requires great caution. If new records are added while deleting, data confusion may occur. The operation is as follows:

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

Set the starting value of the sequence

Generally, the starting value of the sequence is1，But if you need to specify a starting value100，That is, we can achieve it with the following statement:

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

Or you can also achieve it after the table is created with the following statement:

mysql>　ALTER　TABLE　t　AUTO_INCREMENT　=　100;