Mysql auto-increment field rearrangement after mysql deletes the table, the auto-increment field starts from 1.

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.

 

Others:

Some record rows are deleted, so the auto-increment field is not continuous.

1, 3, 4, 5, 7, 10.

 

 

 

 

-------------------------

The truncate command restores the auto-increment field to start from 1, or you can try clearing table_a, cancel auto-increment, save, and add auto-increment again, this is also the method for restoring auto-increment segments to 1.

-----------

Unique ID field of MySql database (automatic Id field)
In database applications, we often use unique numbers to identify records. In MySQL, you can use the AUTO_INCREMENT attribute of the data column

. MySQL supports multiple data tables, and the auto-increment attributes of each data table are different. Here we will introduce the data in various data tables.

Column auto-increment attribute.

ISAM table

If a NULL value is inserted into an AUTO_INCREMENT data column, MySQL automatically generates the next sequence number. Start from 1

And 1 is the base increase.

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 a record is inserted, if a value is explicitly specified for the AUTO_INCREMENT data column, two conditions may occur.

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.

2. If the inserted value is greater than the numbered value, it will be inserted into the data column, and the next number will start from the new value

Add. 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

Increments from this value.

If you use the replace command to modify the existing records in the data table based on the values in the auto_increment data column, that is, the number of auto_increment

The corresponding auto_increment value will not change in the WHERE clause of the replace command. However, if replace is

Modify existing records through other primary key or unique indexes (that is, the auto_increment data column does not appear in

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) --

Will change.

The last_insert_id () function obtains the last number automatically generated by the auto-increment column. However, this function only works with the server during this session.

The generated value is related. 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 auto-incrementing sequences in the same data table. The procedure is as follows: Create

A primary key or unique index consisting of multiple data columns, and include the AUTO_INCREMENT data column in this index

Its last data column. In this way, each of the preceding data columns in this composite index constitutes a unique combination, ending

The AUTO_INCREMENT data column of will generate 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.

The number of columns 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.

Unsigned, 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:

Delete from table_name;
Or
Truncate table table_name

This is because, during the full table operation, MySQL actually performs this optimization operation: First delete all the data and indexes in the data table, however

And then recreate the data table. If you want to delete all data rows and retain the serial number information, you can use the where DELETE command

Suppress MySQL optimization:

Delete from table_name where 1;

This forces MySQL to evaluate a condition expression for each Deleted Data row.

The method that forces MySQL not to reuse the used sequence value is: create another data specifically used to generate 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 generate the sequence number

Insert a null value in the table to generate a number. Then, when inserting data into the main data table, use the last_insert_id () function

Obtain the ID and assign it to the data column in the sequence of the primary 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 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

LAST_INSERT_ID (expr) to insert or modify a data column, and then call the LAST_INSERT_ID () function without parameters,

Then 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. Combined with the return of the LAST_INSERT_ID () function

Value to generate the Count value of different content. The advantage of this method is that the unlock table can generate unique sequence numbers without transactions or locks. No

It will affect normal table operations of other customer programs.

Alter table table_name auto_increment = N;
Note that N can only be greater than the integer of the existing auto_increment. The value smaller than N is invalid.
Show table status like 'table _ name'. We can see that the auto_increment column shows some values.
The step value cannot be changed. You can only use the last_inset_id () function mentioned below

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.

The number of columns 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.

Unsigned, the maximum value is 255. Once the upper limit is reached, auto_increment becomes invalid.

Generate a sequence without auto_increment. You can use the last_insert_id () function with parameters. If you use

Last_insert_id (expr) to insert or modify a data column, and then call the last_insert_id () function without parameters,

Then 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. Combined with the return of the LAST_INSERT_ID () function

Value to generate the Count value of different content. The advantage of this method is that the UNLOCK table can generate unique sequence numbers without transactions or locks. No

It will affect normal table operations of other customer programs.

Pay attention to the following two points:
1. When there is only one column, it won't work!
2. The automatic ID must be used as the primary key!

======================================

To restore the continuous sorting of 1, 2, 3, 4, 5, 6, and 7, you only need to delete the auto-increment fields and add the auto-increment fields again. ------------------------------------------------------------------ Method 1:
Truncate table your table name
// This not only deletes all data, but also relocates the auto-increment Field

Method 2:
Delete from your table name
Dbcc checkident (your table name, reseed, 0)
// Locate the auto-increment field and start from 1.

Method 3:
If you want to save your data, introduce your third method, by qinyi
Use phpMyAdmin to export the database. You will find it in it.
Edit the SQL file, change the auto-increment ID, and then import it.