MySQL AUTO_INCREMENT operation usage

The following articles mainly introduce the actual usage of MySQL AUTO_INCREMENT, as shown in the following figure. This article describes how to use MySQL AUTO_INCREMENT using relevant code, the following describes the specific content of the article.

 
 

  
  
Create table test
  
  
(
  
  
Id int unsigned not null Prima (the most perfect VM management system) ry key AUTO_INCREMENT,
  
  
Username VARCHAR (15) NOT NULL
  
  
)
  
  
AUTO_INCREMENT = 100;
 
 

In database applications, we often use unique numbers to identify records. In MySQL (the best combination with PHP), The AUTO_INCREMENT attribute of the data column can be automatically generated. MySQL (the best combination with PHP) supports multiple data tables. The auto-increment attributes of each data table are different. Here we will introduce the auto-increment attributes of Data columns in various data tables.

ISAM table

If a NULL value is inserted into a MySQL AUTO_INCREMENT data column, MySQL (the best combination with PHP) will automatically generate the next sequence number. The number starts from 1 and increases with 1 as the base number.

Insert 0 into the AUTO_INCREMENT data column to be the same effect as insert NULL value. However, it is not recommended to insert NULL values.

When a record is inserted, if no value is specified for AUTO_INCREMENT, it is equivalent to inserting a NULL value.

When you insert a record, if you specify a value for the AUTO_INCREMENT data column, there are two situations. Case 1: If the inserted value is the same as the existing number, an error occurs because the value of the AUTO_INCREMENT data column must be unique. Case 2: If the inserted value is greater than the numbered value, the inserted value is inserted into the data column, and the next number will increase progressively from the new value. That is to say, you can skip some numbers.

If the maximum value of the auto-increment sequence is deleted, this value is reused when a new record is inserted.

If you use the UPDATE command to UPDATE the auto-increment column, if the column value is the same as the existing value, an error occurs. If the value is greater than the existing value, the next number increases progressively from this value.

If the replace command is used to modify the existing records in the data table based on the values in the AUTO_INCREMENT data column, that is, MySQL AUTO_INCREMENT data is listed in the where clause of the replace command, the corresponding AUTO_INCREMENT value will not change.

However, if the replace command uses other Prima (the most complete virtual host Management System) ry key or unique index to modify existing records (that is, the AUTO_INCREMENT data column does not appear in the where clause of the replace command ), the corresponding AUTO_INCREMENT value -- if it is set to NULL (if it is not assigned a value) -- changes.

The last_insert_id () function obtains the last number automatically generated by the auto-increment column. However, this function is only related to the value generated during the current session of the server. If the AUTO_INCREMENT value is not generated in this session with the server, the function returns 0.

The automatic numbering mechanism of other data tables is based on the mechanism in the ISAM table.

MyISAM data table

After a record with the maximum number is deleted, the number cannot be reused.

You can use the "AUTO_INCREMENT = n" option to specify an auto-increment initial value when creating a table.

You can use the alter table table_name AUTO_INCREMENT = n command to reset the start value of auto-increment.

You can use composite indexes to create multiple independent auto-incrementing sequences in the same data table. The specific method is as follows: create a Prima (the most complete VM management system) ry key or unique index composed of multiple data columns for a data table, and include the AUTO_INCREMENT data column in this index as its last data column. In this way, each of the preceding data columns in the composite index constitutes a unique combination. The AUTO_INCREMENT data column at the end generates a sequence number corresponding to the combination.

HEAP data table

The auto-increment column can be used only when the HEAP data table starts from MySQL (the best combination with PHP) 4.1.

You can use the MySQL AUTO_INCREMENT = n option of the create table statement to set auto-increment values.

You can use the AUTO_INCREMENT = n option of the alter table statement to modify the initial value of auto increment.

Numbers cannot be reused.

HEAP data tables do not support using compound indexes in a data table to generate multiple serial numbers that do not affect each other.

BDB data table

You cannot use the AUTO_INCREMENT = n option of create table or alter table to change the auto-increment initial value.

Reusable ID.

You can use composite indexes in a data table to generate multiple serial numbers that do not affect each other.

InnDB data table

You cannot use the AUTO_INCREMENT = n option of create table or alter table to change the auto-increment initial value.

Numbers cannot be reused.

You cannot use composite indexes in a data table to generate multiple serial numbers that do not affect each other.

When using AUTO_INCREMENT, pay attention to the following points:

AUTO_INCREMENT is an attribute of a data column. It is only applicable to integer data columns.

The data column that sets the AUTO_INCREMENT attribute should be a positive number sequence, so the data column should be declared as UNSIGNED, so that the number of the sequence can be doubled.

The AUTO_INCREMENT data column must have a unique index to avoid repeated sequence numbers.

The AUTO_INCREMENT data column must have the not null attribute.

The maximum number of the AUTO_INCREMENT data column is subject to the data type constraints of this column. For example, the maximum number of the TINYINT data column is 127. If UNSIGNED is added, the maximum value is 255. Once the upper limit is reached, AUTO_INCREMENT becomes invalid.

When a full table is deleted, MySQL AUTO_INCREMENT starts numbering from 1. When a full table is deleted, the following two statements are issued:

 
 

  
  
delete from table_name;ortruncate table table_name 
 
 

This is because, during full table operations, MySQL (the best combination with PHP) actually performs the following optimization: first, delete all the data and indexes in the data table, then, recreate the data table. If you want to delete all data rows and retain sequence numbers, you can use a delete command with where to suppress MySQL (the best combination with PHP) Optimization:

 
 

  
  
delete from table_name where 1; 
 
 

This forces MySQL (the best combination with PHP) to evaluate the conditional expression for each Deleted Data row.

The method that forces MySQL (the best combination with PHP) Not to reuse the used sequence values is: create a data table dedicated to generating the AUTO_INCREMENT sequence, and never delete the records of the table.

When you need to insert a record in the primary data table, First insert a NULL value in the table that specifically generates the sequence number to generate a number. Then, when inserting data into the primary data table, use the LAST_INSERT_ID () function to obtain this number and assign it to the data column of the storage sequence of the master table. For example:

 
 

  
  
insert into id set id = NULL;insert into main set main_id = LAST_INSERT_ID(); 
 
 

You can use the alter command to add a data column with the AUTO_INCREMENT attribute to a data table. MySQL (the best combination with PHP) will automatically generate all numbers.

To rearrange an existing serial number, the simplest way is to delete the column and recreate it. MySQL (the best combination of PHP and MySQL) regenerates a continuous serial number.

Generate a sequence without using MySQL AUTO_INCREMENT. You can use the LAST_INSERT_ID () function with parameters. If you use a LAST_INSERT_ID (expr) with parameters to insert or modify a data column, and then call the LAST_INSERT_ID () function without parameters, the second function call returns the value of expr. The following describes the specific operations of this method:

First, create a data table with only one data row:

 
 

  
  
create table seq_table (id int unsigned not null);insert into seq_table values (0); 
 
 

Then, use the following operations to retrieve the serial number:

 
 

  
  
update seq_table set seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID(); 
 
 

By modifying the constant value in seq + 1, you can generate a sequence with different step sizes. For example, seq + 10 can generate a sequence with a step size of 10.

This method can be used as a counter to insert multiple rows into the data table to record different counting values. Then, use the return value of the LAST_INSERT_ID () function to generate the Count values of different content. The advantage of this method is that the UNLOCK table can generate unique sequence numbers without transactions or locks. Normal table operations of other customer programs are not affected.