[MySQL] How to re-index auto_increment id of a table to make use of resting sequence ids when it reached its max limit.

how-to-re-idex-auto-increment-id.sql

--
-- How to re-index the auto_increment id.
--

-- 0) Create an original table:
CREATE TABLE `hoge` (
  `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `hoge`(`name`) VALUES(now());
INSERT INTO `hoge`(`name`) VALUES(now());
INSERT INTO `hoge`(`name`) VALUES(now());
INSERT INTO `hoge`(`name`) VALUES(now());
INSERT INTO `hoge`(`name`) VALUES(now());
INSERT INTO `hoge`(`name`) VALUES(now());
DELETE FROM `hoge` WHERE `id` <= 3;
-- ...
                                  
-- 1) Create new table named `hoge2` and copy `hoge` to it:
DROP TABLE `hoge2`;
CREATE TABLE `hoge2` (
  `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Copy all row without auto_increment `id` column:
INSERT INTO `hoge2`(`name`) select `name` from `hoge`;

-- 2) Set auto_increment seq with hoge2's max id:
SET @m = (SELECT MAX(id) + 1 FROM `hoge2`); 
SET @s = CONCAT('ALTER TABLE `hoge2` AUTO_INCREMENT=', @m);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

-- 3) Drop the original table then rename `hoge2` to it:
DROP TABLE `hoge`;
ALTER TABLE `hoge2` RENAME `hoge`;

-- Now you can insert some rows using the resting sequence id:
INSERT INTO `hoge`(`name`) VALUES(now());
INSERT INTO `hoge`(`name`) VALUES(now());
INSERT INTO `hoge`(`name`) VALUES(now());
...