mysql sequence 生成 & 遇到的问题

sql语句自动插入有规则的uuid（雪花算法)

建议还是给表结构放一个默认的自增_id

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;

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

cpp 的方法

// 用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));
}

备忘

存储过程获取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