mysql将id重新修改为递增

场景

好比我有个配置表:

CREATE TABLE `config` (
  `id` int NOT NULL AUTO_INCREMENT,
  `config_key` varchar(20)  NOT NULL,
  `config_value` varchar(500)  NOT NULL,
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
1
2
3
4
5
6
7
8
9

里面有3条数据:

INSERT INTO `test`.`config`(`id`, `config_key`, `config_value`, `modified_time`, `created_time`, `remark`) VALUES (1, 'aaa', 'aaa', '2023-08-10 10:17:45', '2023-08-10 10:17:45', NULL);
INSERT INTO `test`.`config`(`id`, `config_key`, `config_value`, `modified_time`, `created_time`, `remark`) VALUES (5, 'bbb', 'bbb', '2023-08-10 10:22:02', '2023-08-10 10:17:51', NULL);
INSERT INTO `test`.`config`(`id`, `config_key`, `config_value`, `modified_time`, `created_time`, `remark`) VALUES (10, 'ccc', 'ccc', '2023-08-10 10:22:05', '2023-08-10 10:20:16', NULL);

1
2
3
4

我先在想将这个id按照1,2,3这样排,且后面增加也是递增的,如果数据量少可以手动改,但是如果我有100条,然后乱了,手动排就很不现实,

解决,排序的话可以先按照一定大小改一下,然后将id字段删掉,再重新生成即可

-- 删除id
alter table config drop id;
-- 新增主键id
alter table config add id int primary key auto_increment first;
1
2
3
4

清空表数据,并将自增id改为1开始

TRUNCATE TABLE table_name;
ALTER TABLE table_name AUTO_INCREMENT = 1;
1
2