What should I do if mysql's self-increasing ID is not continuous?

In mysql, "AUTO_INCREMENT" can be used to solve the problem of discontinuous auto-increment ids. "AUTO_INCREMENT" is used to set the automatic growth of the primary key. You only need to set the auto-increment of the id to 1. The syntax is " ALTER TABLE table name AUTO_INCREMENT=1".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What should I do if mysql’s self-increasing id is not continuous?

MySQL sets unique fields in navicat:

Index (select fields) >>> Index type (UNIQUE)

When there is a unique field in the table and the primary key id is incremented, the unique field already exists when inserting new data, and the insertion fails.

But at this time, the id has been incremented by 1, and the data is inserted again. The problem of discontinuous IDs.

-- After execution, it does not indicate that the inserted id is set to 1; instead, the inserted id is set to the maximum id value in the table, 1.

 ALTER TABLE `table` AUTO_INCREMENT =1;

Add the above sql statement before the insert into operation, and set the insertion id to the id 1 that exists in the current table;

auto_increment is used for automatic growth of the primary key, starting from 1 , when you delete the first record and insert the second hop data, the primary key value is 2, not 1.

For example:

create table `test`
(
`id` int(10) not null auto_increment, -- 表示自增列
`name` varchar(20) not null,
primary key(`id`)
)

auto_increment = 1; -- Indicates the starting size of the auto-increment -- In this way, you can create a table `test` with the id as the auto-increment column

-- Execute the statement insert into test (`name`) values ​​('name');

-- You can insert a row of data as: 1 'name'

Extended information:

When using AUTO_INCREMENT, you should pay attention to the following points:

1. AUTO_INCREMENT is an attribute of data columns and only applies to integer type data columns.

2. The data column for which the AUTO_INCREMENT attribute is set should be a positive sequence, so the data column should be declared as UNSIGNED, so that the number of the sequence can be doubled.

3. The AUTO_INCREMENT data column must have a unique index to avoid repeated serial numbers (that is, the primary key or part of the primary key). The AUTO_INCREMENT data column must have the NOT NULL attribute.

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

5. When deleting the entire table, MySQL AUTO_INCREMENT will restart the numbering from 1.

This is because when performing a full table operation, MySQL (the best combination with PHP) actually performs such an optimization operation: first delete all data and indexes in the data table, and then rebuild the data table. .

If you want to delete all data rows and retain the sequence number information, you can use a delete command with where to suppress the optimization of MySQL (the best combination with PHP): delete from table_name where 1;

Recommended learning: mysql video tutorial

The above is the detailed content of What should I do if mysql's self-increasing ID is not continuous?. For more information, please follow other related articles on the PHP Chinese website!