Mysql auto-increment primary key id is not processed in this way

Mysql auto-increment primary key id does not increase step by step

1. Introduction

When adding data to the MySQL database and using ON DUPLICATE KEY UPDATE to update the data, the ID may not increase step by step, but increase intermittently.

For example, if the id is 10, the next time it is added, it may be 15 or another number. The interval between the two numbers is the number of times ON DUPLICATE KEY UPDATE is executed, that is, ON DUPLICATE KEY UPDATE increments the primary key of the table by one when executing the update.

As shown in the figure

2. Problem Introduction

When adding and modifying the same table, I used two mapper interface methods, that is, I used two ON DUPLICATE KEY UPDATE operations, which will cause the primary key of the table to increment when you update it. The next time you insert, it will not increase step by step.

3. Solution

Split the insert and update of the database.

Advantages and disadvantages of auto-increment primary key

Auto-increment primary key

This method uses the auto-increment numeric field provided by the database as the auto-increment primary key. Its advantages are:

(1) The database is automatically numbered, fast, and incrementally increases, and is stored in order, which is very beneficial for retrieval;

(2) Digital type, which takes up little space, is easy to sort, and is convenient to pass in the program;

(3) If you add records through a non-system, you do not need to specify this field and do not need to worry about primary key duplication.

In fact, its disadvantages come from its advantages, and the disadvantages are as follows:

(1) Because of the automatic growth, it is troublesome to manually insert a record with a specified ID. Especially when the system is integrated with other systems and data import is required, it is difficult to ensure that the ID of the original system does not conflict with the primary key (assuming that the old system is also digital). Especially when a new system is launched, the old and new systems exist in parallel, and the databases are heterogeneous and require two-way synchronization, the auto-increment primary key will be your nightmare;

(2) During system integration or cutover, if the primary keys of the new and old systems are different and are numeric, the primary key data type will be modified, which will also lead to modifications to other tables associated with foreign keys, with equally serious consequences.

(3) If the system is also digital, in order to distinguish between new and old data during import, you may want to add a character identifier (such as "o", old) before the primary key of the old data to indicate that it is old data. Then the automatically increasing digital type faces another challenge.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.