我们的应用程序调用一个存储过程将其数据规范化为引用表,然后在主表中插入一条记录,部分包含映射到引用表的值,部分包含映射到引用表的id。这是存储过程之一:

CREATE PROCEDURE `sp_name`(IN valueIn varchar(100), OUT valueOut int)
BEGIN
declare maxid int;
declare countid int;
select max(id) into valueOut from tableName where fieldName=valueIn;
IF valueOut is NULL
THEN
  start transaction with consistent snapshot;
  select count(*) into countid from tableName where fieldName=valueIn;
  IF countid=0
  THEN
    insert into tableName (fieldName) values (valueIn);
    select  last_insert_id() into valueOut;
  ELSE
    select max(id) into valueOut from tableName where fieldName=ValueIn;
  end IF;
commit;
end IF;
END

当手动调用时,这很好,但是当在生产中调用时,在引用表中会出现多个重复值。
事务隔离级别是可重复读取的。
参考表:
CREATE TABLE `tableName` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `fieldName` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8

对字段名使用唯一键约束不是一个好的选择。我们已经尝试过这样做,但是我们没有得到重复的值,而是看到自动增量跳过了ID。我们正在尝试保留ID,这样在涉及数据类型时就不需要过度分配。我们的主表很大(数十亿),所以我们必须有效地使用数据类型。
有人了解这种现象吗?

最佳答案

如果你想为auto_increment构建自己的替代品,你需要清除很多障碍。您将发现可串行化、并发性、性能(通常与锁定相关)等问题。
我认为最简单的解决方案可能是对bigint unsigned类型的列使用auto_increment。无符号整数的最大值是4294967295:大约4x10^ 9。无符号BigIt的最大值是1844 67407370955 1615:大约1.8x10^ 19。
自动递增仍然会跳过id号,但这是设计上的,在1.8x10^19的范围内不会造成问题。
在你选择这条路径之前,先用你的客户端软件测试大量数据。有些人仍然不能优雅地处理bigint,不管是否签名。

关于mysql - 引用表中的值重复,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/22698219/

10-09 14:34