Mycat -05 DB 方式自增主键

分片表part_mod,区模分片方式，主键为数据库自增。具体配置如下：

1、配置MyCat的Server.xml

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

2.配置schema.xml （rule.xml 同之前的分片讲解）

<table name="part_mod" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
                           rule="mod-long" />

3.配置sequence_db_conf.properties

PART_MOD=dn1
# 表名必须大写

4.登录到数据库dn1 上创建sequence表以及函数

## 表和函数必须建在一个数据库下。但是可以不跟part_mod 表放在一起。

#所有操作都在 db3 上
#创建 MYCAT_SEQUENCE 表
DROP TABLE IF EXISTS MYCAT_SEQUENCE;
– name sequence 名称
– current_value 当前 value
– increment 增长步长!   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;

#插入一条 sequence
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('part_mod',
100, 100);

#创建相关 function
#获取当前 sequence 的值 (返回当前值,增量)
DROP FUNCTION IF EXISTS mycat_seq_currval;
DELIMITER ;;
CREATE DEFINER=`root`@`%` 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 DEFINER=`root`@`%` 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 DEFINER=`root`@`%` 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 ;

重启mycat 进行验证 mycat restart

1.登录到9066 端口查看主键的方式

2.登录8066 端口创建part_mod 表并插入数据验证。

mysql> create table part_mod (id INT PRIMARY KEY,NAME VARCHAR(16));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.17 sec)

mysql> select next value for MYCATSEQ_PART_MOD; 
+-----+
| 201 |
+-----+
| 201 |
+-----+
1 row in set (0.05 sec)

mysql> select * from part_mod;
+-----+------+
| id  | NAME |
+-----+------+
| 200 | db3  |
+-----+------+
1 row in set (0.07 sec)
mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.00 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.01 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.00 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.01 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.00 sec)

mysql> select * from part_mod;
+-----+------+
| id  | NAME |
+-----+------+
| 200 | db3  |
| 203 | db3  |
| 206 | db3  |
| 202 | db2  |
| 204 | db1  |
| 205 | db2  |
+-----+------+
6 rows in set (0.00 sec)

mysql> delete from part_mod;
Query OK, 6 rows affected (0.01 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.03 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.00 sec)

mysql> insert into part_mod (id,name) values (next value for MYCATSEQ_PART_MOD,database());
Query OK, 1 row affected (0.00 sec)

mysql> select * from part_mod;
+-----+------+
| id  | NAME |
+-----+------+
| 207 | db1  |
| 208 | db2  |
| 209 | db3  |
+-----+------+
3 rows in set (0.00 sec)

mysql> select next value for MYCATSEQ_PART_MOD;
+-----+
| 210 |
+-----+
| 210 |
+-----+
1 row in set (0.00 sec)