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