Global primary key auto increment

I. self increasing method of local files

                   

1. Modify fragmentation strategy

                        

2. Modify the server.xml file

sequnceHandlerType in   server.xml file is used to configure the primary key generation type

So we need to change the value of sequnceHandlerType to 0 first.

3. Introduction to sequence_conf.properties

                     

#Wed Oct 16 07:40:44 CST 2019
COMPANY.MAXID=2000
GLOBAL.MAXID=20000
COMPANY.HISIDS=
CUSTOMER.MAXID=2000
HOTNEWS.CURID=1000
ORDER.MINID=1001
CUSTOMER.HISIDS=
HOTNEWS.MINID=1001
GLOBAL.CURID=10002
ORDER.MAXID=2000
COMPANY.CURID=1000
CUSTOMER.CURID=1000
COMPANY.MINID=1001
GLOBAL.MINID=10001
HOTNEWS.MAXID=2000
CUSTOMER.MINID=1001
GLOBAL.HISIDS=
HOTNEWS.HISIDS=
ORDER.HISIDS=
ORDER.CURID=1000

The main thing is that global. Maximum = 20000 global. Curid = 10002 global. Minid = 10001 can be set by yourself.

4. Test implementation

Replace the primary key field with next value for mycatseq "global in the insert statement

 insert into t_user(id,name,age)values(next value for MYCATSEQ_GLOBAL,'HG-93',23)

II. Local time stamp auto increment method

  using timestamps, we do not need to assign policies or choose other fragmentation policies.

1. Modify the server.xml file

   change sequnceHandlerType in the server.xml file to 2

2. Restart mycat

                  

3. Insert data test

 insert into t_user(id,name,age)values(next value for MYCATSEQ_GLOBAL,'HG-93',23)

Timestamp too long change id to varchar type.

Build successful~

III. database auto increment mode

1. Create sequence table and related functions

The third way is to create an auto increasing table structure in a database managed by Mycat to maintain related data. The related scripts are officially provided as follows:

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 ;
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('GLOBAL', 100000, 100);
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 ;
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 ;
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 ;

We execute these scripts on demo2

2. Modify server.xml

3. Modify the sequence DB conf.properties file

   because the logical library corresponding to demo2 is dn2, we need to modify here

4. test

   restart service and insert data test

 insert into t_user(id,name,age)values(next value for MYCATSEQ_GLOBAL,'hg-93',23)

The generation of primary key is successful. In addition to these three ways, you can also maintain the self increasing primary key through 'zookeeper', which can be implemented by yourself.