Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "how to solve mysql self-increasing id discontinuity". The editor shows you the operation process through an actual case. The operation method is simple and fast, and it is practical. I hope this article "mysql self-increasing id discontinuity how to solve" article can help you solve the problem.

In mysql, "AUTO_INCREMENT" is available to solve the problem of self-increment id discontinuity, "AUTO_INCREMENT" is used to set the automatic growth of primary keys, simply set the self-growth of id to 1, and 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.

Mysql self-increasing id discontinuity what to do

MySQL sets unique fields in navicat:

Index (select field) > > Index type (UNIQUE)

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

But at this time, id has increased by + 1, and inserting data again causes id discontinuity.

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

ALTER TABLE `table` AUTO_INCREMENT = 1

Add the above sql statement before the insert into operation to set the insert id to the id+1 that exists in the current table

Auto_increment is used for automatic primary key growth, 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,-- indicates the self-incremented `name` varchar (20) not null,primary key (`id`))

Auto_increment = 1;-- indicates the starting size of self-increment-- so that a table `test` can be created, and id is a self-incrementing column.

-- execute the statement insert into test (`name`) values ('name')

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

Extended data:

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

1. AUTO_INCREMENT is an attribute of a data column and is only applicable to integer type data columns.

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

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

4. The maximum number of AUTO_INCREMENT data columns is restricted by the data type of the column. For example, the maximum number of TINYINT data columns is 127. if UNSIGNED is added, the maximum number is 255. Once the upper limit is reached, the AUTO_INCREMENT will fail.

5. When you delete the whole table, MySQL AUTO_INCREMENT will start numbering again from 1.

This is because when performing a full table operation, MySQL (the best combination with PHP) actually does 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 rows of data and want to retain serial number information, use a delete command with where to suppress the optimization of MySQL (the best combination with PHP): delete from table_name where 1

This is the end of the introduction of "how to solve the id discontinuity since mysql". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.