Mycat创建及使用数据库方式序列

1. 选择一个数据库，并创建序列表

DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE `MYCAT_SEQUENCE` (
  `NAME` varchar(50) NOT NULL comment  "名称",
  `current_value` int(11) NOT NULL comment "当前值",
  `increment` int(11) NOT NULL DEFAULT '100' comment "步长",
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 插入一条记录作为逻辑表的序列号

insert into MYCAT_SEQUENCE(name, current_value, increment) values ('TESTDBSEQ', 100000, 100); 

3. 创建生成序列的函数

• 创建当前sequence函数

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 ;

• 设置sequence函数

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 ;

• 获取下一个sequence函数

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 ;

4. server.xml指定序列生成方式为数据库方式

<system><property name="sequnceHandlerType">1</property></system>

5. 配置 sequence_db_conf.properties ：TESTDBSEQ 为全局序列的名称，dn1 表示分片节点

TESTDBSEQ=dn1

6. 使用MYCATSEQ_TESTDBSEQ

insert into test1(id,xm) values(next value for MYCATSEQ_TESTDBSEQ,'zhangsan'); 
insert into test1(id,xm) values(next value for MYCATSEQ_TESTDBSEQ,'lisi');