Simple tutorial on using sequences in MySQL _ MySQL

This article mainly introduces a simple tutorial on using sequences in MySQL, which is the basic knowledge in MySQL beginners. This article provides examples based on PHP and Perl scripts, if you need a friend, refer to the following sequence as a group of integers 1, 2, 3 ,.... Databases that are frequently used in sequences, because many applications require each row in a table to contain a unique value and sequence to provide a simple method to generate. This chapter describes how to use sequences in MySQL.
Use auto-incrementing columns:

The simplest way to use sequences in MySQL is to define an AUTO_INCREMENT column, and leave the rest to MySQL for processing.
Instance:

Try the following example. After the table is created, it inserts several rows into the table because it is automatically incremented by MySQL because it is not a required record ID.

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:

LAST_INSERT_ID () is an SQL function that can be used on any client to understand how to issue SQL statements. Otherwise, PERL and PHH scripts provide unique functions to retrieve the auto-incrementing value of the last record.
PERL example:

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by the query. Access to this attribute is through a database handle or statement handle, depending on how the query is made. The following example references it through the database handle:

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

PHP example:

After the AUTO_INCREMENT value is generated, the retrieved value is called mysql_insert_id ():

mysql_query ("INSERT INTO insect (name,date,origin)VALUES('moth','2001-09-14','windowsill')", $conn_id);$seq = mysql_insert_id ($conn_id);

Recompile an existing sequence:

This may occur when a record is deleted from a table and all records to be reordered are deleted. This can be done by using a simple technique, but be very careful if the table is participating with other tables.

If it is determined that the AUTO_INCREMENT column is inevitably resequencing, this is to delete the column from the table and then add it again. The following example demonstrates how to use this technique to rename the id value of a BUG:

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

The startup sequence is in a special value:

By default, MySQL starts sequence 1, but can specify any other numbers when creating a table. In the following example, MySQL starts from 100.

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 create a TABLE and set the initial sequence value alter table.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;