mycat全局序列号数据库方式

1、server.xml

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

2、schema.xml

<table name="z_test" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long"/>
<table name="mycat_sequence" primaryKey="name" dataNode="dn2"/>

3、sequence_db_conf.properties

#sequence stored in datanode
Z_TEST=dn2

4、创建存储步长的表MYCAT_SEQUENCE

DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE (name VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(name)) ENGINE=InnoDB;

5、增加步长100的主键数量

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('Z_TEST', -99, 100);

 6、函数

6.1、获取当前sequence的值

DROP FUNCTION IF EXISTS mycat_seq_currval;
DELIMITER $
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64)     CHARSET utf8
DETERMINISTIC
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 ;

6.2、设置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
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END $
DELIMITER ;

6.3、 获取下一个sequence值

DROP FUNCTION IF EXISTS mycat_seq_nextval;
DELIMITER $
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64)     CHARSET utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END $
DELIMITER ;

注：表和函数存储过程可任选一主库分库进行操作，设置sequence_db_conf.properties的时候，设置相应的库就可以