MySQL sequence usage

MySQL sequence usage

MySQL sequence is a set of integers: 1, 2, 3, ..., due to a The data table can only have one field with an auto-increment primary key. If you want to implement automatic increment of 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 sequences in MySQL is to use MySQL AUTO_INCREMENT to define columns.

Example

In the following example, the data table insert is created. The ID in insect can be automatically grown 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)

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

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

PERL Example

Use the mysql_insertid attribute to get the value of AUTO_INCREMENT. The example is as follows:

$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 insertion 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 delete multiple records in the data table and want to perform the AUTO_INCREMENT column on the remaining data To rearrange, you can do this by deleting the incremented column and then re-adding it. However, you must be very careful with this operation. If new records are added while deleting, data chaos 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 is 1, but if you need to specify A starting value of 100, then we can implement it through 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
);

mysql> ALTER TABLE t AUTO_INCREMENT = 100