Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

A new feature of MySQL8: a detailed explanation of the persistence of self-increasing primary keys

2025-01-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Preface

The fact that the primary key is not persisted is an early bug, as can be seen from its id number on the official bug website (https://bugs.mysql.com/bug.php?id=199). It was proposed by Peter Zaitsev (now Percona CEO) in 2003. It has a long history and is notorious.

First of all, reproduce it intuitively.

Mysql > create table T1 (id int auto_increment primary key); Query OK, 0 rows affected (0.01sec) mysql > insert into T1 values (null), (null), (null); Query OK, 3 rows affected (0.01sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from T1 Matte Muscat + | id | +-- + | 1 | 2 | 3 | 3 | +-+ rows in set (0.00 sec) mysql > delete from T1 where id=3;Query OK, 1 row affected (0.36 sec) mysql > insert into T1 values (null) Query OK, 1 row affected (0.35 sec) mysql > select * from T1 + | id | +-+ | 1 | | 2 | | 4 | +-+ rows in set (0.01 sec)

Although the record with an id of 3 is deleted, when the null value is inserted again, instead of reusing the deleted 3, 4 is allocated.

Delete the record with an id of 4, restart the database, and reinsert a null value.

Mysql > delete from T1 where id=4;# service mysqld restartmysql > insert into T1 values (null); Query OK, 1 row affected (0.00 sec) mysql > select * from T1 Tantra Musashi + | id | +-+ | 1 | | 2 | 3 | +-+ rows in set (0.00 sec)

As you can see, the newly inserted null value allocates 3. According to the operation logic before restart, 5 should be allocated here.

This is the bug in which the self-increasing primary key is not persisted. The reason is that the allocation of self-incrementing primary keys is determined by a counter inside the InnoDB data dictionary, which is only maintained in memory and not persisted to disk. When the database is restarted, the counter is initialized in the following way.

SELECT MAX (ai_col) FROM table_name FOR UPDATE

The solution of MySQL 8.0

Persist the counter of the self-incrementing primary key to redo log. Each time the counter changes, it is written to the redo log. If the database is restarted, InnoDB initializes its memory value based on the counter information in redo log. To minimize the impact on system performance, counters are written to redo log and are not immediately refreshed. For more information, please refer to https://dev.mysql.com/worklog/task/?id=6204

Common scenarios where problems occur because the self-incremented primary key is not persisted:

1. The business uses the self-increasing primary key as the business primary key, and at the same time, the business requires that the primary key can not be repeated.

two。 The data will be archived. Primary key conflicts may occur during the archiving process.

Therefore, it is strongly recommended that you do not use a self-increasing primary key as a business primary key. Excluding these two scenarios, in fact, the problem of non-persistence of the self-increasing primary key is not very big, far less than the "notoriety" imagined.

Finally, a solution in the archiving scenario is given.

Create a stored procedure to initialize table2 (online table) based on the maximum value of the table1 (archive table) self-incrementing primary key. This stored procedure can be placed in the file specified by the init_file parameter, and the SQL in that file is executed when the database starts.

DELIMITER;; CREATE PROCEDURE `auto_increment_ fromtable2` (IN table1 VARCHAR, IN table2 VARCHAR) BEGINset @ qry = concat ('SELECT @ max1: = (`id` + 1) FROM `', table1,' `ORDER BY `id`DESC LIMIT 1politics'); prepare stmt from @ qry;execute stmt;deallocate prepare stmt; set @ qry = concat ('SELECT @ max2: = (`id` + 1) FROM `', table2,' `ORDER BY `id`DESC LIMIT 1witch'); prepare stmt from @ qry;execute stmt;deallocate prepare stmt IF @ max1 < @ max2 THEN set @ qry = concat ('alter table `, table1,' `auto_increment=',@max2); prepare stmt from @ qry;execute stmt;deallocate prepare stmt;SELECT' updated' as `status`; elseSELECT'no update needed' as `status`; END IF;END;; DELIMITER

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.

Views: 222

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report