The MySQL sequence is used

The MySQL sequence is used

MySQL sequence is a set of integers: 1, 2, 3, ..., since a data table can only have one field self-adding primary key, if you want to implement automatic addition of other fields, you can use MySQL sequence to achieve.

In this chapter we'll show you how to use mySQL sequences.

Use AUTO_INCREMENT

The easiest way to use sequences in MySQL is to use MySQL AUTO_INCREMENT define columns.

Instance

The data table is created in the following example, and the id in the insect does not need to specify a value for 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)

Gets AUTO_INCREMENT value

In MySQL clients, you can use the LAST_INSERT_ID() function in SQL to get the value of the self-added column in the last inserted table.

Functions are also provided in PHP or PERL scripts to get the value of the self-added column in the last inserted table.

PERL instance

Use the mysql_insertid property to get the AUTO_INCREMENT of the file. Here's an example:

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

PhP instance

PHP mysql_insert_id () function to get the value of the column in the AUTO_INCREMENT executed 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 the sequence

If you delete multiple records in the data table and want to rearrange the AUTO_INCREMENT columns of the remaining data, you can do so by deleting the self-added columns and then adding them again. H owever, this is done with great care, and if new records are added at the same time as deletion, there may be data confusion. Here's what it looks like:

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

Sets the start value of the sequence

In general, the start value of the sequence is 1, but if you need to specify a start value of 100, we can do so by following the 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 do this by following the following statement after the table has been created successfully:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;