Mysql唯一索引线上故障记录

时间：2022-02-21 02:53:41

问题现象：

Mysql插入一条数据时，未指定自增键的值却报错：自增键重复，无法插入！

执行SQL

INSERT INTO `test`.`test_sort`(`id`, `name`, `age`, `birthday`) VALUES 
(10027, '测试', 23, '2020-01-07 12:23:11')

报错：

INSERT INTO `test`.`test_sort`(`id`, `name`, `age`, `birthday`) VALUES 
(10027, '测试', 23, '2020-01-07 12:23:11')
> 1062 - Duplicate entry '38' for key 'sort'
> 时间: 0.001s

表的创建语句：

CREATE TABLE `test_sort`  (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '年龄',
  `birthday` datetime(0) NULL DEFAULT NULL COMMENT '生日',
  `sort` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `sort`(`sort`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 39 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

这里可以发现，sort字段在表中是自增键，通过唯一索引 sort 来实现，而我在插入新行的时候没有指定 sort 字段的值，那么它理论上会采用当前自增 B-Tree 上最大数字 1，这里却提示他冲突了。

问题还原

表里面原来的数据如下：

INSERT INTO `test_sort` VALUES (10001, '张三', 23, '2020-01-07 12:23:11', 1);
INSERT INTO `test_sort` VALUES (10002, '王五', 23, '2020-01-07 12:23:11', 9);
INSERT INTO `test_sort` VALUES (10003, '赵六', 23, '2020-01-07 12:23:11', 10);
INSERT INTO `test_sort` VALUES (10004, '钱七', 23, '2020-01-07 12:23:11', 11);
INSERT INTO `test_sort` VALUES (10005, '刘八', 23, '2020-01-07 12:23:11', 12);
INSERT INTO `test_sort` VALUES (10006, '冯九', 23, '2020-01-07 12:23:11', 13);
INSERT INTO `test_sort` VALUES (10007, '李四', 23, '2020-01-07 12:23:11', 44);
INSERT INTO `test_sort` VALUES (10008, '李四1', 23, '2020-01-07 12:23:11', 36);
INSERT INTO `test_sort` VALUES (10009, '王五1', 23, '2020-01-07 12:23:11', 38);
INSERT INTO `test_sort` VALUES (10010, '赵六1', 23, '2020-01-07 12:23:11', 40);
INSERT INTO `test_sort` VALUES (10011, '钱七1', 23, '2020-01-07 12:23:11', 41);
INSERT INTO `test_sort` VALUES (10012, '刘八1', 23, '2020-01-07 12:23:11', 46);
INSERT INTO `test_sort` VALUES (10013, '冯九1', 23, '2020-01-07 12:23:11', 47);
INSERT INTO `test_sort` VALUES (10014, '李四1', 23, '2020-01-07 12:23:11', 48);
INSERT INTO `test_sort` VALUES (10015, '王五1', 23, '2020-01-07 12:23:11', 21);
INSERT INTO `test_sort` VALUES (10016, '赵六1', 23, '2020-01-07 12:23:11', 22);
INSERT INTO `test_sort` VALUES (10017, '钱七1', 23, '2020-01-07 12:23:11', 23);
INSERT INTO `test_sort` VALUES (10018, '刘八1', 23, '2020-01-07 12:23:11', 24);
INSERT INTO `test_sort` VALUES (10019, '冯九1', 23, '2020-01-07 12:23:11', 25);
INSERT INTO `test_sort` VALUES (10021, '测试', 23, '2020-01-07 12:23:11', 28);
INSERT INTO `test_sort` VALUES (10022, '测试', 23, '2020-01-07 12:23:11', 30);
INSERT INTO `test_sort` VALUES (10023, '测试', 23, '2020-01-07 12:23:11', 32);
INSERT INTO `test_sort` VALUES (10024, '测试', 23, '2020-01-07 12:23:11', 33);
INSERT INTO `test_sort` VALUES (10025, '测试', 23, '2020-01-07 12:23:11', 34);
INSERT INTO `test_sort` VALUES (10026, '测试', 23, '2020-01-07 12:23:11', 37);

这里在数据插入时，我通过 Navicat 手动修改了表中 sort 字段的值，改成一个比原来最大值。此时再重新添加数据，当自增键的值增长到该值时，就会报该错误。

解决方法

删除原来用于自增的唯一索引 sort,然后重新创建该索引。记得删除后点保存，之后添加索引，保存

Mysql唯一索引线上故障记录

删除时出现该提示是因为自增长必须依赖索引存在，索引应该先去掉该键的自增长功能。在重新添加索引后打开该功能

受《高性能Mysql》的启发

Mysql唯一索引线上故障记录

Mysql唯一索引线上故障记录

这本书里面有句：通过一个不做任何操作的 ALTER 操作来重建表。而我们的删除-重新添加操作和他有异曲同工之妙。

启发

这次问题给我带来的启发如下，也请同行们参考

  1. 不到万不得已不要手动去修改数据库的唯一索引字段
  2. 如果修改了，修改之后通过删除 重新添加索引的方式避免线上服务出错
  3. 书中自有黄金屋，如果不是之前读过自增键、索引相关的知识，遇到此事便不能及时解决，可能会造成无法挽回的损失，书到用时方恨少...

相关文章