Code Bye

mysql 重新组织sequence的问题

drop table if EXISTS test01;
create table test01 
(seq_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `index` INT UNSIGNED NOT NULL);
drop table if EXISTS new_test01;
create table new_test01 
(`index` INT UNSIGNED NOT NULL);
insert into new_test01 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
DROP TRIGGER IF EXISTS `trigger_01`;
	CREATE
	    TRIGGER `trigger_01` BEFORE INSERT
	    ON `test01`
	    FOR EACH ROW BEGIN
		DECLARE	`seq_id` BIGINT UNSIGNED;
		DECLARE	`now_millis` BIGINT UNSIGNED;
		DECLARE	`our_epoch` BIGINT UNSIGNED DEFAULT 1446307200000;
		SET `now_millis` = (SELECT UNIX_TIMESTAMP(NOW(3)) * 1000);
		SET `seq_id` = (SELECT AUTO_INCREMENT FROM information_schema.`TABLES` WHERE table_schema = "testauto_1" AND table_name = "test01");
		SET NEW.seq_id = (SELECT ((`now_millis` - `our_epoch`) << 23) | (MOD(2, 256) << 15) | MOD (`seq_id`, 32768));
	END;
DROP PROCEDURE IF EXISTS `pro_01`;
create PROCEDURE pro_01(in num int unsigned)
BEGIN
DECLARE i int DEFAULT 0;
	REPEAT
		insert into `testauto_1`.test01 (`index`) ( select `index` from testauto_1.new_test01 );
		set i = i+1;
	UNTIL i>num	END REPEAT;
END;
call pro_01(7000);
select * from test01 where `index`=1;
-- 到1638次的时候一定会失败,这个为什么?
drop table if EXISTS test02;
create table test02 
(seq_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `index` INT UNSIGNED NOT NULL);
drop table if EXISTS test02_seq;
create table test02_seq 
(seq_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
DROP TRIGGER IF EXISTS `trigger_02`;
	CREATE
	    TRIGGER `trigger_02` BEFORE INSERT
	    ON `test02`
	    FOR EACH ROW BEGIN
		DECLARE	`seq_id` BIGINT UNSIGNED;
		DECLARE	`now_millis` BIGINT UNSIGNED;
		DECLARE	`our_epoch` BIGINT UNSIGNED DEFAULT 1446307200000;
		SET `now_millis` = (SELECT UNIX_TIMESTAMP(NOW(3)) * 1000);
		SET `seq_id` = (SELECT AUTO_INCREMENT FROM information_schema.`TABLES` WHERE table_schema = "testauto_1" AND table_name = "test02_seq");
		SET NEW.seq_id = (SELECT ((`now_millis` - `our_epoch`) << 23) | (MOD(2, 256) << 15) | MOD (`seq_id`, 32768));
		INSERT into test02_seq values (NULL);
	END;
DROP PROCEDURE IF EXISTS `pro_02`;
create PROCEDURE pro_02(in num int unsigned)
BEGIN
DECLARE i int DEFAULT 0;
	REPEAT
		insert into `testauto_1`.test02 (`index`) ( select `index` from testauto_1.new_test01 );
		set i = i+1;
	UNTIL i>num	END REPEAT;
END;
call pro_02(7000);
select * from test02 where `index`=1;
-- 改成这样就可以解决这个问题,但是没弄明白为什么

test01表到1638次的时候一定会失败,这个为什么?
看了数据发现是test01的自增量原因是trigger的设置改变了,但是还没有到越界失效的情况,为什么循环一定量必定失败呢?而且失败的报告里面是主键冲突,但实际上冲突的主键在表里面并不存在在test01里面
虽然改成test02那种方式可以解决问题,但还是不清楚第一种出问题的原因
求帮忙

解决方案

100

引用 6 楼 kinly_jiang 的回复:
Quote: 引用 3 楼 yupeigu 的回复:

本人觉得会不会是并发的时候 出现了这种极端的情况

本人也怀疑这个,但是不知道怎么验证这个怀疑,也没有找到相关资料

应该是触发器中的 这段代码导致 的,假如有大量并发的insert 语句,就会同时运行这段代码,通过字典表来获取当前自增列的值,产生了相同的seq-id:
SET `seq_id` = (SELECT AUTO_INCREMENT FROM information_schema.`TABLES` WHERE table_schema = “testauto_1” AND table_name = “test01”);


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql 重新组织sequence的问题