MySQL use sequence

MySQL is a sequence of integers: 1, 2, 3, ..., as a data table can have only one field increment primary keys, if you want to implement additional fields are automatically increased, you can use MySQL sequences to achieve.

In this chapter we will describe how to use MySQL sequences.

Use AUTO_INCREMENT

MySQL simplest method is to use a sequence to define the use of MySQL AUTO_INCREMENT column.

Examples

The following example creates a data table insect, insect without specifying the value of id can automatically increase.

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 MySQL client you can use the SQL LAST_INSERT_ID () function to retrieve the last value inserted into the table's auto-increment.

In PHP or PERL script also provides a corresponding function to get the value for the last inserted table auto-increment of.

PERL examples

Use mysql_insertid property to get the value of AUTO_INCREMENT. Examples are as follows:

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

PHP Examples

PHP to get the value into the SQL statement executed AUTO_INCREMENT column by mysql_insert_id () function.

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 delete the data in the table multiple records, and hope the remaining data AUTO_INCREMENT columns to rearrange, then you can delete columns from growing, then re-add it to achieve. However, the operation to be very careful if you have a new record is added at the same time removed, there may be data chaos. 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 start value of the sequence

Under normal circumstances sequence start value of 1, but if you need to specify a start value of 100, then we can achieve the following statement:

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
);

Or you can create a table in the success achieved by the following statement:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;