An example of the implementation of deleting data self-increasing IDs in MySQL database

After deleting data from MySQL database, adding new data will cause self-increasing IDs to be discontinuous.

Solution

1. After deleting a row of data, the data will be automatically increased and the ID value will be reduced by one ()

update Table name set id = id - 1 where id > #{deleteId}

2. Update the next self-increment ID value

When the setting value is less than max(id) , it will be automatically set to max(id)+1, so it is generally good to set to 1

alter table Table name auto_increment = 1

Performance optimization

The above method must be executed every time the data is deleted, and performance optimization can be achieved through MySql's timing task-reset the self-increasing ID at zero every day.

1. Define the stored procedure for resetting the self-increment ID

create procedure p_reset_id()
begin
    set @i = 0;
    update Table name set id = (@i := @i + 1);
    alter table Table name auto_increment = 1;
end

2. Turn on the event scheduler

show variables like 'event_scheduler';
set global event_scheduler = on;

3. Define events/timed tasks

create event e_reset_id
on schedule every 1 day 
starts '2024-10-12 00:00:00'
do call p_reset_id();

This is the article about the implementation example of deleting data self-increasing IDs in MySQL database. This is all about this. For more related contents related to MySQL deleting data self-increasing IDs in MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!