Mycat自增主键设置

1.在server.xml中,将sequnceHandlerType设置为1

<property name="sequnceHandlerType">1</property>

2.在schema.xml中,table中增加属性autoIncrement值为true,添加mycat_sequence表

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
    <!-- random sharding using mod sharind rule -->
    <!-- autoIncrement="true" 属性-->
    <table name="tt2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4,dn5" rule="mod-long" />
    <table name="mycat_sequence" primaryKey="name" dataNode="dn1" />
</schema>

3.在sequence_db_conf.properties中,依赖全局序列,增加序列,与table名称相同全大写

TT2=dn1

4.创建mycat_sequence表

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;  
  • name:sequence名称
  • currenct_value:当前value
  • increment:增长步长
    注:MYCAT_SEQUENCE必须大写

5.插入sequence记录

INSERT INTO MYCAT_SEQUENCE(name, current_value, increment) VALUES ('TT2', 1, 100);

6.创建存储函数,必须在同一个数据库中创建

-- 获取当前sequence的值 (返回当前值,增量)  
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 ;  
      
-- 设置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  
DETERMINISTIC  
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  
DETERMINISTIC  
BEGIN  
UPDATE MYCAT_SEQUENCE  
SET current_value = current_value + increment WHERE name = seq_name;  
RETURN mycat_seq_currval(seq_name);  
END $  
DELIMITER ;  

7.在mysql中定义自增主键

CREATE TABLE `tt2` (  
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  
  `nm` INT(10) UNSIGNED NOT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;  

8.插入记录

insert into tt2(nm) values (99);

FROM:
1.MyCAT自增字段和返回生成的主键ID的经验分享
2.MyCAT 性能测试
3.mycat分布式mysql中间件(自增主键)
4.MyCAT全局序列号

Table of Contents