Mycat journey (3) ---- global serial number of Mycat, mycat ---- mycat

Mycat journey (3) ---- global serial number of Mycat, mycat ---- mycat
I. Local file Mode

Principle: in this way, MyCAT configures the sequence to a file. When the sequence configuration is used, MyCAT changes the sequence_conf.properties file in classpath.

The current sequence value.

Configuration method:

• In sequence_conf.properties, configure:

Make the following configuration in the sequence_conf.properties file:
# Default global sequence
GLOBAL. HISIDS =
# Minimum value
GLOBAL. MINID = 10001
# Maximum value
GLOBAL. MAXID = 20000
# Current Value
GLOBAL. CURID = 10000

• Server. xml configuration:

<! -- SequnceHandlerType = 0: Use the local file to configure the global serial number of mycat. The sequence_conf.properties file sequnceHandlerType = 1 indicates: configure the global serial number of mycat using database tables --> <property name = "sequnceHandlerType"> 0 </property>

• Restart the mycat service after the configuration is complete.

Test:

<! -- SequnceHandlerType = 0: Use the local file to configure the global serial number of mycat. The sequence_conf.properties file sequnceHandlerType = 1 indicates: configure the global serial number of mycat using database tables --> <property name = "sequnceHandlerType"> 1 </property>

Database Configuration:

1) create a MYCAT_SEQUENCE table

-Create a table for storing sequence

DROP TABLE IF EXISTS MYCAT_SEQUENCE;

-Name sequence name

-Current_value: Current value

-Increment growth step! It can be understood as the number of sequence mycat reads in the database at a time. When these are used up, it will be read from the database again next time.

/* CREATE a TABLE storing sequence */create table MYCAT_SEQUENCE (name varchar (50), current_value int not null, increment int not null default 100, primary key (NAME )) ENGINE = INNODB;

 

Insert into MYCAT_SEQUENCE (NAME, current_value, increment) VALUES ('global', 100000,100 );

-- Sequence Function Structure: 'mycat _ seq_currval' -- get the current sequence Value (Return Current value, incremental) -- ------------------------------ drop functionif exists mycat_seq_currval; create function 'mycat _ seq_currval' (seq_name VARCHAR (50) RETURNS varchar (64) CHARSET latin1 deterministicbegin declare retval VARCHAR (64); SET retval = "-1, 0 "; SELECT concat (CAST (current_value as char), ",", CAST (increment as char) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name; RETURN retval; END

 

-- Sequence FUNCTION Structure: 'mycat _ seq_setval '-- set the sequence value -- sequence drop function if exists mycat_seq_setval; create function 'mycat _ seq_setval' (seq_name VARCHAR (50), value INTEGER) RETURNS varchar (64) CHARSET latin1 deterministicbegin declare retval VARCHAR (64); DECLARE inc INT; SET inc = 0; SELECT increment INTO inc FROM MYCAT_SEQUENCE WHERE name = seq_name; UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name; SELECT concat (CAST (value as CHAR), ",", CAST (inc as CHAR) INTO retval; RETURN retval; END

 

-- Construct FUNCTION Structure: 'mycat _ seq_nextval' -- get the value of the next sequence -- revoke drop function if exists mycat_seq_nextval; create function 'mycat _ seq_nextval '(seq_name VARCHAR (50 )) RETURNS varchar (64) CHARSET latin1 deterministicbegin declare retval VARCHAR (64); DECLARE val BIGINT; DECLARE inc INT; DECLARE seq_lock INT; set val =-1; set inc = 0; SET seq_lock =-1; SELECT GET_LOCK (seq_name, 15) into seq_lock; if seq_lock = 1 then SELECT current_value + increment, increment INTO val, inc FROM MYCAT_SEQUENCE WHERE name = seq_name for update; if val! =-1 then UPDATE MYCAT_SEQUENCE SET current_value = val WHERE name = seq_name; end if; SELECT RELEASE_LOCK (seq_name) into seq_lock; end if; SELECT concat (CAST (val-inc + 1) as CHAR), ",", CAST (inc as CHAR) INTO retval; RETURN retval; END

 

Note: The MYCAT_SEQUENCE table and the above three functions must be placed on the same node. Run the function directly on the database of the specific node.

Test:

insert into travelrecord(id,name)values(next value for MYCATSEQ_GLOBAL,'hexmith');

Insert into travelrecord (id, name) values (next value for MYCATSEQ_GLOBAL, 'mycat ');

In the previous test, the data step I set in the MYCAT_SEQUENCE table is 100, which is consistent here.