mycat自增序列DB方式

1 自增序列
mycat_sequence
vim schema.xml
<table name="mycat_sequence" primaryKey="name" dataNode="dn1"/>
<table name="company_auto" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />


##以下是已经存在的
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />


<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.237.161:3306" user="root" password="admin">
<!-- can have multi read hosts -->
<!--<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />-->
</writeHost>
<writeHost host="hostS1" url="192.168.237.162:3306" user="root" password="admin" />
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>


vim server.xml 
<system><property name="sequnceHandlerType">1</property></system>
注：sequnceHandlerType 需要配置为 1，表示使用数据库方式生成 sequence.


vim sequence_db_conf.properties ##添加表名，需要大写
COMPANY_AUTO=dn1


mysql -h127.0.0.1 -uroot -p123456 -P8066
##创建表
create table mycat_sequence(name varchar(50) not null primary key,current_value bigint not null,increment int not null default 1)engine=innodb;


insert into mycat_sequence(name,current_value,increment)values('company_auto',100000,10);


##创建函数，需要在具体数据库上执行
DELIMITER $$




USE `db1`$$




DROP FUNCTION IF EXISTS `mycat_seq_currval`$$




CREATE DEFINER=`root`@`%` 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 ;


DELIMITER $$




USE `db1`$$




DROP FUNCTION IF EXISTS `mycat_seq_setval`$$




CREATE DEFINER=`root`@`%` 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 ;
DELIMITER $$




USE `db1`$$




DROP FUNCTION IF EXISTS `mycat_seq_nextval`$$




CREATE DEFINER=`root`@`%` 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 ;




mysql -h127.0.0.1 -uroot -p123456 -P8066
##创建表
create table company_auto(id int not null auto_increment primary key,name varchar(20));
#查看当前自增序列值
select next value for MYCATSEQ_company_auto;
##插入数据
insert into company_auto(name)values('qqqqqqq');




##查看自增序列
select * from mycat_sequence;