MySQL sequence Auto_increment

The MySQL sequence is a set of integers: 1, 2, 3, ..., because a data table can only have one field self-increment primary key, if you want to implement other fields also automatically increase, you can use the MySQL sequence to implement.

In this chapter we will show you how to use the MySQL sequence.

using Auto_increment

The simplest way to use sequences in MySQL is to use MySQL auto_increment to define columns.

Instance

Data table insect is created in the following instance, and the ID in insect does not need to specify a value for autogrow.

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 () Not NULL # where collected); Query OK, 0 rows affected (0.02 sec) mysql> INSERT into insect (id,name,date,origin) VALUES, (NULL, ' housefly ', ' 2 001-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:0mysql> 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 last_insert_id () function in SQL to get the value of the last inserted table in the self-increment column.

The corresponding function is also provided in the PHP or Perl script to get the value of the last inserted table in the self-increment column.

Perl instances

Use the Mysql_insertid property to get the value of the 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 instance

PHP uses the mysql_insert_id () function to get the value of the Auto_increment column in the inserted SQL statement that is executed.

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 a datasheet and want to rearrange the auto_increment columns of the remaining data, you can do so by removing the self-contained columns and then adding them again. However, the operation is very careful, if the deletion of a new record added, there may be data chaos. The operation is as follows:

mysql> ALTER TABLE insect DROP id;mysql> ALTER TABLE insect,    ADD ID INT UNSIGNED not NULL auto_increment FI RST,    ADD PRIMARY KEY (ID);

set the start value of a sequence

In general, the start value of the sequence is 1, but if you need to specify a start value of 100, then we can do this with the following statement:

mysql> CREATE TABLE Insect    , id INT UNSIGNED not NULL auto_increment = +,    PRIMARY KEY (  ID),    name VARCHAR (+) NOT NULL, # Type of insect-date    date not NULL, # Date collected,    origin VARCHAR (+) not NULL # where collected);

Or you can do this with the following statement, after the table is created successfully:

mysql> ALTER TABLE t auto_increment = 100;

　　

Original address: http://www.manongjc.com/mysql/mysql_using_sequences.html

MySQL Related reading:

MySQL Div division modulo operation and MoD take-out operation

MYSQ the modulo operation of Div division

MySQL MOD () explanation of the remainder function example

mysql least () function gets the minimum value

MySQL greatest () function gets the largest value in the collection

MySQL sequence Auto_increment