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;

-- 改成这样就可以解决这个问题，但是没弄明白为什么

论坛发了帖子：http://bbs.csdn.net/topics/391883766

存储过程获取sql执行的错误信息：

drop procedure if exists `procedure_test_01`;
create procedure `procedure_test_01` (out xmsg varchar(1024))
BEGIN
    DECLARE _sql varchar(500);
    DECLARE _code char(5) DEFAULT '00000';
    DECLARE _msg TEXT;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION GET DIAGNOSTICS CONDITION 1 _code=RETURNED_SQLSTATE, _msg=MESSAGE_TEXT;
    START TRANSACTION;
    -- do sql ....
    IF _code='00000' THEN
        COMMIT;
        SET xmsg='successed';
    ELSE
        ROLLBACK;
        SET xmsg=CONCAT('failed,error=',_code,', message=',_msg);
    END IF;
    SELECT xmsg AS resmsg_out;
END

// 用C++11实现自取seq，有些情况是异步数据库，但是程序需要先知道seq做key
unsigned long long g_seq_id_x = 0;

unsigned long long get_seq_id_x()
{
	unsigned long long _mill_now = std::chrono::duration_cast<std::chrono::milliseconds>(std::chrono::system_clock::now().time_since_epoch()).count();
	unsigned long long _mill_end = _mill_now - 1446307200000;
	g_seq_id_x += 1;
	return ((_mill_end << 23) | (1 << 18) | (2 << 10) | (g_seq_id_x & 0x3FF));
}