How do I fully rewrite the SQL index?

I am terribly sorry if this is a supremely easy question. It's just such a weird case I have trouble even figuring out how to write that in google. I just can't. I'll describe the situation and what I want to do - I don't know how to put that as a question...

The situation is this. I have a mySQL table: service_logs. There I have the log_id as the primary key with AUTO_INCREMENT set to it. So all the various logs there have log_id 1, 2, 3, 4, 5 and so on with various data. But across the history, many individual logs were deleted. So now I have: log_id 1: content log_id 2: content log_id 10: content log_id 11: content log_id 40: content and so on.

I want to fill the gaps in that. I want to have the entry nr. 10 reassigned the 3rd number, then entry nr. 11 assigned the 4th number, and so on. I don't want to have gaps in there.

And yes I know it's dumb and shouldn't be done. I just have a friend who needs these without gaps for some of his Excel stuff :/

Solution

Yes this is a bad idea and not necessary as you can always add a row_number to get your wanted result to export it.

That said.

You can remove auto_increment and primary key
Renumber the column
Add autoincrement and primary key
and set the "correct" number

it must be clear at the time no further actions should be run with the server

If you only want consquitove numbers you can also choose toe "make a seciond" log table with row_numbers in it, it is the sample CREATE TABLE log2

CREATE TABLe log(log_id int PRIMARY KEY AUTO_INCREMENT)

ALTER TABLE log MODIFY log_id INT ;
ALTER TABLE log DROP PRIMARY KEY;

INSERT INTO log VALUES (1),(2),(5),(10)

CREATE TABLE log2 SELECT ROW_NUMBER() OVER(ORDER BY log_id ASC) log_id,'abs' FROm log

UPDATE log
JOIN (SELECT @rank := 0) r
SET log_id=@rank:=@rank+1;

SELECT * FROM log

| log_id |
| -----: |
|      1 |
|      2 |
|      3 |
|      4 |

ALTER TABLE log MODIFY log_id INT  PRIMARY KEY AUTO_INCREMENT;

✓

 SELECT @max := MAX(log_id)+ 1 FROM log;

  PREPARE stmt FROM 'ALTER TABLE log AUTO_INCREMENT = ?';
  EXECUTE stmt USING @max;

  DEALLOCATE PREPARE stmt;

| @max := MAX(log_id)+ 1 |
| ---------------------: |
|                      5 |

SELECT * FROM log2

log_id | abs
-----: | :--
     1 | abs
     2 | abs
     3 | abs
     4 | abs

db<>fiddle here