|
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的时候，设置相应的库就可以
|