mycat配置全局系列号，采用db方式

以你之姓@ 2022-06-01 07:16 112阅读 0赞

先要在对应datanode节点上创建相关表(我这里用的是dn1):

#创建表
    CREATE TABLE MYCAT_SEQUENCE (
       name VARCHAR (50) NOT NULL comment  "名称",
       current_value INT NOT NULL  comment  "当前值",
       increment INT NOT NULL DEFAULT 100  comment  "步长",
       PRIMARY KEY (name)
    ) ENGINE = INNODB;
    
    #插入对应自增长表
    insert into MYCAT_SEQUENCE(name,current_value,increment) values('T_USER',19,5);
    
    
    
    #取当前squence的值
    DROP FUNCTION IF EXISTS mycat_seq_currval;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50))RETURNS VARCHAR(64) CHARSET 'utf8'
    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 值
    DROP FUNCTION IF EXISTS mycat_seq_setval;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64) CHARSET 'utf8'
    BEGIN
       UPDATE MYCAT_SEQUENCE SET current_value = VALUE    WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END$$
    DELIMITER ;
    
    #取下一个sequence的值
    DROP FUNCTION IF EXISTS mycat_seq_nextval;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) CHARSET 'utf8'
    BEGIN
    UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment 
    WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END$$
    DELIMITER ;

修改数据库my.cnf：

数据库配置文件修改my.ini
    
    log_bin_trust_function_creators=1
    # 忽略大小写
    lower_case_table_names=1

修改schema.xml：(添加主键和自增长配置primarykey和autoIncrement属性 )

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
    	<schema name="tanlei" checkSQLschema="false" sqlMaxLimit="100"> 
    		<table name="t_user" subTables="t_user$1-3" dataNode="dn1" rule="mod-long" primaryKey="id" autoIncrement="true"/> 
    	</schema> 
    	<dataNode name="dn1" dataHost="localhost1" database="tanleidb" /> 
    	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" 
    	 dbDriver="native" switchType="1" slaveThreshold="100"> 
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM1" url="localhost:3306" user="root" password="root" /> 
    	</dataHost>
    
    </mycat:schema>

修改sequence\_db\_conf.properties：

注意对应shcema.xml中table的表名要大写，我这里是T\_USER, 对应创建sequence表和存储函数的节点是dn1.

#sequence stored in datanode
    GLOBAL=dn1
    T_USER=dn1
    CUSTOMER=dn1
    ORDERS=dn1

进入mycat客户端，插入数据:

insert into t\_user(userName,age,createTime,updateTime) values('tanlei2',23,now(),now());

查询数据会发现该条数据id已经自增长:

select \* from t\_user;

不过我们线上id都是用一个只有一列主键自增长id的表维护的！