[mysql tutorial] MySQL sequence usage

MySQL sequence usage

MySQL sequence is a set of integers: 1, 2, 3, .... Since a data table can only have one field with an auto-increment primary key, if you want to implement other fields as well To achieve automatic increase, you can use MySQL sequences to achieve it.

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

Use 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 insect is created. The ID in insect can be automatically increased 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)

Get the AUTO_INCREMENT value

In the MySQL client, you can use the LAST_INSERT_ID() function in SQL to get 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 instances

Use the mysql_insertid attribute to obtain 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 Use 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 delete multiple records in the data table and want to rearrange the AUTO_INCREMENT columns of the remaining data, you can delete the auto-incremented columns , and then add it again to achieve 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 use the following statement Implementation:

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 also implement it through the following statement after the table is successfully created:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

The above is [mysql tutorial] The content of MySQL sequence usage, please pay attention to the PHP Chinese website (www.php.cn) for more related content!