mysql 数据库 序列号 自增长

在同一个节点任何一个数据库上
新建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 ;

修改

ADMIN=dataNode1

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