How does MySQL create sequences? MySQL database creation auto-increment sequence statement

MySQLHow to create a sequence?MySQL databaseCreate an auto-incrementing sequence statement

MySQL sequence usage

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

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

Use AUTO_INCREMENT

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

Examples

The data table Insect is created in the following example, in which no value is specified for automatic growth.

mysql > CREATE TABLE昆虫
     - > （- > id INT UNSIGNED NOT NULL AUTO_INCREMENT ，- > PRIMARY KEY （id ），- > name VARCHAR （30 ）NOT NULL ，＃昆虫类型- > 日期DATE NOT NULL ，＃收集日期- > origin VARCHAR （30 ）NOT NULL ＃where collected ）; 查询OK ，0 行受影响（0.02 秒）
mysql > 
    
    
     
     
    

 INSERT INTO insects （id ，name ，date ，origin ）VALUES
     - > （NULL ，'housefly' ，'2001-09-10' ，'kitchen' ），- > （NULL ，'millipede' ，'2001-09-10 ' ，'车道' ），- > （NULL ，'grasshopper' ，'2001-09-10' ，'front yard' ）; 查询OK ， 
     
     
 秒）记录：3 重复：0 警告：0 
mysql > 选择* 从昆虫ORDER BY ID ; + ---- + ------------- + ------------ + ------------ + | id | 名字         | 日期        | 起源      | + ---- + ------------- + ------------ + ------------ + | 1 | 家蝇     | 2001 - 09 - 10 | 厨房     | | 2 | 千足虫   
       



     
   | 2001 - 09 - 10 | 车道    | | 3 | 蚱蜢| 2001 - 09 - 10 | 前院| + ---- + ------------- + ------------ + ------------ + 3 行中集（0.00 秒）  
     

  

Get 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-incremented column inserted into the table.

Corresponding functions are also provided in the PHP or PERL script to obtain the value of the last auto-increment column inserted into the table.

PERL instance

Use the mysql_insertid property to get the value of 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 example

PHP obtains the value of the AUTO_INCREMENT column in the executed insert SQL statement through the mysql_insert_id() function.

mysql_query （“INSERT INTO昆虫（名字，日期，起源）
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 column of the remaining data, you can do so by deleting the auto-incrementing column and then adding it again.However, this operation has to be very careful, if at all.At the same time of deletion, new records are added, and there may be data confusion. The operations are as follows:

mysql > ALTER TABLE昆虫DROP ID ; 
mysql > ALTER TABLE昆虫
     - > ADD ID INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST ，- > ADD PRIMARY KEY （id ）;
    

Set the start value of the sequence

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

mysql > CREATE TABLE昆虫
     - > （- > id INT UNSIGNED NOT NULL AUTO_INCREMENT ，- > PRIMARY KEY （id ），- > name VARCHAR （30 ）NOT NULL ，- > date DATE NOT NULL ，- > origin VARCHAR （30 ）NOT NULL
 ）engine = innodb auto_increment = 100 charset = utf8 ; 
    
    
     
    
    

Or you can also use the following statement after the table is created successfully:

mysql > ALTER TABLE t AUTO_INCREMENT = 100 ;