I have an SQL table with an id column that uses auto-increment. When I delete a row (e.g., where the id is 6), the IDs of the remaining rows do not change, and there is no longer a row with id 6. How can I automatically rearrange the IDs of the remaining rows so they are continuous (e.g., 1, 2, 3, … without gaps) after a deletion?

I’m using MySQL.

DO $$
DECLARE
  new_id INTEGER := 1;
BEGIN
  FOR record IN SELECT * FROM your_table ORDER BY id LOOP
    INSERT INTO temp_table (id, column1, column2, ...) 
    VALUES (new_id, record.column1, record.column2, ...);
    new_id := new_id + 1;
  END LOOP;
END $$;

I want alter of this one