Skip to content

Instantly share code, notes, and snippets.

@akirattii
Last active July 14, 2023 06:37
Show Gist options
  • Save akirattii/16af2995a2c84fa71c452956c8b14181 to your computer and use it in GitHub Desktop.
Save akirattii/16af2995a2c84fa71c452956c8b14181 to your computer and use it in GitHub Desktop.
[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-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());
...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment