Mycat自增主键设置

发表于 | 分类于

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

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

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

1
2
3
4
5
6
<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名称相同全大写

1
TT2=dn1

4.创建mycat_sequence表

1
2
3
4
5
6
7
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记录

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 获取当前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中定义自增主键

1
2
3
4
5
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.插入记录

1
insert into tt2(nm) values (99);

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