mycat mysql 配合索引_【mycat】mycat中配合mysql自增主键的使用

本文实现一个简单的demo，展示mycat配合mysql实现分库时自增主键的使用。

前提说明：mycat目前提供了自增主键的使用，但是如果对应的mysql节点上的数据表没有定义auto_increment的话，mycat的自增主键也是无效的。

温馨提示：关于mycat的安装、配置和简单测试可以查阅如下文档http://blog..net/smilefyx/article/details/72810531

1、在schema.xml中增加测试表

修改schema.xml配置文件，我们在mycat提供的TESTDB逻辑表中增加一个名为tb_sq_test的逻辑表，该逻辑表的配置如下：

可以看到该逻辑表的数据会被分配到dn1,dn2,dn3的数据节点上。

2、创建测试表

使用navicat链接到mycat。我们选中TESTDB逻辑库并创建一个名为tb_sq_test的数据表用于完成本文的测试。该表中有一个自增的主键id和一个name属性。创建表的sql语句如下：

CREATE TABLE `tb_sq_test` (

`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL,

PRIMARY KEY (`id`)

);

之后执行如下命令：

explain CREATE TABLE `tb_sq_test` (

`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL,

PRIMARY KEY (`id`)

);

会得到如下结果

----------+

| DATA_NODE | SQL |

+-----------+----------------------------------------------------------------------------------------------------------------------------------+

| dn1 | CREATE TABLE `tb_sq_test` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) |

| dn2 | CREATE TABLE `tb_sq_test` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) |

| dn3 | CREATE TABLE `tb_sq_test` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) |

+-----------+----------------------------------------------------------------------------------------------------------------------------------+

3 rows in set

可以看到在三个数据库上都创建了名为tb_sq_test的数据表；

3、修改sequence生成方式

mycat提供了多种sequence生成方式，本文需要展示的是数据库生成sequence方式。修改server.xml配置文件，修改sequnceHandlerType参数的值为1，表示使用数据库方式生成sequence：

1

4、创建MYCAT_SEQUENCE的表和相关函数

选择一个数据节点(物理的数据库节点)创建MYCAT_SEQUENCE的表和相关函数，注意这里的MYCAT_SEQUENCE的表和相关函数需要存放在同一个节点上。本文选择db1来创建相关的数据表和函数。使用navicat直接连接到mysql数据库。选中db1数据库并运行如下命令创建相关的数据表和函数：

USE db1;

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;

-- ----------------------------

-- Function structure for `mycat_seq_currval`

-- ----------------------------

DROP FUNCTION IF EXISTS `mycat_seq_currval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET latin1

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 ;

-- ----------------------------

-- Function structure for `mycat_seq_nextval`

-- ----------------------------

DROP FUNCTION IF EXISTS `mycat_seq_nextval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET latin1

DETERMINISTIC

BEGIN

UPDATE MYCAT_SEQUENCE

SET current_value = current_value + increment WHERE name = seq_name;

RETURN mycat_seq_currval(seq_name);

END

;;

DELIMITER ;

-- ----------------------------

-- Function structure for `mycat_seq_setval`

-- ----------------------------

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 latin1

DETERMINISTIC

BEGIN

UPDATE MYCAT_SEQUENCE

SET current_value = value

WHERE name = seq_name;

RETURN mycat_seq_currval(seq_name);

END

;;

DELIMITER ;

5、插入测试表的sequence数据

插入测试表sequence生成数据到上述创建的表格中，相关命令如下：

INSERT INTO mycat_sequence (name, current_value, increment) VALUES ('tb_sq_test', 0, 100);

6、指定sequence相关配置所在的节点

修改sequence_db_conf.properties配置文件中的相关配置，增加sequence的节点配置信息，否则mycat是不知道sequence的表和函数在哪个节点上的。编辑该属性文件，增加如下值：

TB_SQ_TEST=dn1注意：这里的key必须为表名的大写，小写是不行的。

7、重启mycat

完成上述的配置后需要重启mycat，否则服务也不会生效。在mycat的安装目录下的bin目录下运行如下命令重启mycat服务：

mycat.bat restart

8、测试

使用navicat链接到mycat数据库，执行如下命令插入一条记录到数据库中：

insert tb_sq_test(name)values('helloworld2');之后执行查询命令可以查看插入的内容：

select * from tb_sq_test;