How to automatically add ID numbers in MySQL

We often use mysql tools to create databases so that we can easily implement id field auto-increment. Below we will show you how to make the field auto-increment method.

We often use mysql tools to create databases so that we can easily implement id field auto-increment. Below we will show you how to make the field auto-increment method.

Unique Numbers are often used in applications. In MySQL, The AUTO_INCREMENT attribute of the field can be automatically generated. MySQL supports multiple data tables, each of which has different auto-increment attributes.

ISAM table

If a NULL value is inserted into an AUTO_INCREMENT data column, MySQL automatically generates the next sequence number. The number starts from 1 and increases with 1 as the base number.
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 field, 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, the 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 primary key or unique indexes 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 primary key or unique index composed of multiple data columns for the 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-incrementing column is allowed for HEAP Data Tables starting from MySQL4.1.
The auto-increment value can be set through the AUTO_INCREMENT = n option of the create table statement.
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, AUTO_INCREMENT starts numbering from 1. When a full table is deleted, the following two statements are issued:
From table_name; or table table_name this is because MySQL optimized the entire table by deleting all the data and indexes in the data table and then recreating 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 optimization:
Delete from table_name where 1; this forces MySQL to evaluate the conditional expression for each Deleted Data row.
The method that forces MySQL not to reuse the used sequence value 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 (); Use the alter command to add a data column with the AUTO_INCREMENT attribute to a data table. MySQL automatically generates all numbers.
To rearrange an existing serial number, the simplest way is to delete the column and recreate it. MySQL will regenerate the continuous serial number sequence.
Generate a sequence without 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:
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 retrieve the serial number using the following operations:

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 will not be affected