MySQL sequence AUTO_INCREMENT, mysqlautoincrement

MySQL sequence AUTO_INCREMENT, mysqlautoincrement

MySQL sequence is a group of integers: 1, 2, 3 ,..., since a data table can only have one Field Auto-incrementing primary key, if you want to implement auto-increment of other fields, you can use the MySQL sequence.

This chapter describes how to use MySQL sequence.

  Use AUTO_INCREMENT

The simplest method to use sequences in MySQL is to use MySQL AUTO_INCREMENT to define columns.

Instance

In the following example, the data table insect is created. You do not need to specify a value for the id in insect to 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: 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

On the MySQL client, you can use the LAST_INSERT_ID () function in SQL to obtain the value of the auto-increment column in The Last inserted Table.

The PHP or PERL script also provides functions to obtain the values of the auto-incrementing column in The Last inserted Table.

PERL instance

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value. Example:

$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 obtain the value of the AUTO_INCREMENT column in the SQL statement to be inserted.

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 from a data table and want to rearrange the AUTO_INCREMENT columns of the remaining data, you can delete the auto-increment columns and add them again. However, this operation should be very careful. If a new record is added at the same time, data confusion may occur. The procedure 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 sequence start value

Generally, the starting value of the sequence is 1, but if you need to specify a starting value of 100, we can use 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);

Alternatively, you can use the following statement after the table is created successfully:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

　　

Address: http://www.manongjc.com/mysql/mysql_using_sequences.html

Read about mysql:

Mysql div division modulo operation and mod remainder operation

Modulo operation of mysq div Division

Mysql MOD () Evaluate the remainder function instance

Obtain the minimum value by using the mysql least () function.

The mysql GREATEST () function obtains the maximum value in the set.