mycat global serial number

mycat global serial number

mycat installation

Local file mode

sequence_conf.properties

GLOBAL_SEQ.HISIDS=
GLOBAL_SEQ.MINID=1001
GLOBAL_SEQ.MAXID=1000000000
GLOBAL_SEQ.CURID=1000

server.xml

<system>
	<!--0=本地文件,1=数据库,2=时间戳,3=zk,4=zk递增-->
	<property name="sequnceHandlerType">0</property>
</system>

Usage: insert into t_user (id,name) VALUES (next value for MYCATSEQ_GLOBAL,'2')
Advantages: fast
Disadvantages: id duplication errors will occur in mycat cluster environment

Database way

server.xml

<system>
	<!--0=本地文件,1=数据库,2=时间戳-->
	<property name="sequnceHandlerType">1</property>
</system>

sequence_db_conf.properties

USER=dn1
Insert picture description here

Create table MYCAT_SEQUENCE
in dn1, which is centos101's user1 library. Create table MYCAT_SEQUENCE (must be capitalized).
This is a pitfall , because we configure mysql basically to configure lower_case_table_names=1, that is, ignore case and convert to lowercase, and if you put lower_case_table_names =1 Comment out and restart mysql, you will find that it can't be started. So do not configure this when installing mysql.

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;
--这个USER,即对应sequence_db_conf.properties的USER
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('USER', 10, 
100);

Create three functions

创建相应的函数一:传入序列名,获取当前sequence的值
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 ;


创建相应的函数二:给指定sequence设定当前值(指定具体value)
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 ;


创建相应的函数三:给指定sequence设定当前值(当前值=原当前值+步长)

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 ;

Restart mycat test:

--MYCATSEQ_是固定写法,USER就是对应sequence_db_conf.properties的USER
insert into t_user (id,name) VALUES (next value for MYCATSEQ_USER,'2')

Local timestamp

server.xml

<system>
	<!--0=本地文件,1=数据库,2=时间戳-->
	<property name="sequnceHandlerType">2</property>
</system>

sequence_time_conf.properties: The default configuration is just fine

WORKID=0-31 任意整数
DATAACENTERID=0-31 任意整数

The WORKID and DATAACENTERID of each mycat configuration under multiple mycat nodes are different, forming a unique identifier, a total of 32*32=1024 combinations are supported

Use: INSERT INTO t_user (NAME)VALUES('2')