mysql 数据库序列号自增长

太过爱你忘了你带给我的痛 2023-07-07 03:47 20阅读 0赞

在同一个节点任何一个数据库上  
新建MYCAT\_SEQUENCE 表

CREATE TABLE MYCAT_SEQUENCE(
      name VARCHAR(50) NOT NULL COMMENT 'sequence名称',
      current_value INT NOT NULL COMMENT '当前value',
      increment INT NOT NULL DEFAULT 100 COMMENT '增长步长',
      PRIMARY KEY(name)
    ) ENGINE=InnoDB;
    
     INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ADMIN', 3, 1);

sql 创建三个sql函数

DROP FUNCTION IF EXISTS mycat_seq_currval;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50))RETURNS VARCHAR(64) CHARSET 'utf8'
    BEGIN
    DECLARE retval VARCHAR(64);
    SET retval='-999999999,NULL';
    SELECT CONCAT(CAST(current_value AS CHAR),',',CAST(increment AS CHAR)) INTO retval FROM
    MYCAT_SEQUENCE WHERE NAME = seq_name;
    RETURN retval;
    END$$
    DELIMITER ;
    
    
    
    
    DROP FUNCTION IF EXISTS mycat_seq_setval;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64) CHARSET 'utf8'
    BEGIN
       UPDATE MYCAT_SEQUENCE SET current_value = VALUE    WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END$$
    DELIMITER ;
    
    
    
    DROP FUNCTION IF EXISTS mycat_seq_nextval;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) CHARSET 'utf8'
    BEGIN
    UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment 
    WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END$$
    DELIMITER ;

修改

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1NDMwNTYz_size_16_color_FFFFFF_t_70]  
ADMIN=dataNode1  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1NDMwNTYz_size_16_color_FFFFFF_t_70 1]

INSERT INTO  tb_admin (id,user_name)  VALUES (NEXT VALUE FOR MYCATSEQ_ADMIN,"xxx")

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1NDMwNTYz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200220173308690.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1NDMwNTYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1NDMwNTYz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200220173338133.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI1NDMwNTYz,size_16,color_FFFFFF,t_70