mycat分布式mysql中间件（自增主键）

全局序列号是MyCAT提供的一个新功能，为了实现分库分表情况下，表的主键是全局唯一，而默认的MySQL的自增长主键无法满足这个要求。全局序列号的语法符合标准SQL规范，其格式为：

next value for MYCATSEQ_GLOBAL

其中MYCATSEQ_GLOBAL是序列号的名字，MyCAT自动创建新的序列号，免去了开发的复杂度，另外，MyCAT也提供了一个全局的序列号，名称为：MYCATSEQ_GLOBAL。

注意，MYCATSEQ_必须大写才能正确识别。

MyCAT温馨提示：实践中，建议每个表用自己的序列号，序列号的命名建议为MYCATSEQ _tableName_ID_SEQ。

SQL中使用说明

自定义序列号的标识为：MYCATSEQ_XXX ,其中XXX为具体定义的sequence的名称，应用举例如下：

使用默认的全局sequence :

insert into tb1(id,name) values(next value for MYCATSEQ_GLOBAL,'micmiu.com');

使用自定义的 sequence :

insert into tb2(id,name) values(next value for MYCATSEQ_MY1,'micmiu.com');

获取最新的值

Select next value for MYCATSEQ_xxx

注意：

序列：MYCATSEQ_MYCAT

配置：MYCAT=dn1

表为：MYCAT 也就是”表名“=序列号后半部分的名字（MYCATSEQ_xxx 中的xxx）

目前全局序列号提供了本地文件跟数据库模式及本地基于时间戳算法的三种方式：

1. 本地文件：

1. 配置server.xml   
     <property name="sequnceHandlerType">0</property>
2. 配置sequence_conf.properties
    #default global sequence
   GLOBAL.HISIDS=
   GLOBAL.MINID=10001
   GLOBAL.MAXID=20000
   GLOBAL.CURID=10000

   # self define sequence
   COMPANY.HISIDS=
   COMPANY.MINID=1001
   COMPANY.MAXID=2000
   COMPANY.CURID=1000

测试：

2. 数据库方式：

1. 配置server.xml   
     <property name="sequnceHandlerType">1</property>
2. 配置sequence_db_conf.properties
   #sequence stored in datanode
   GLOBAL=dn1
   MYST=dn1

3.数据库本地创建脚本： 
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 ;
INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 0, 100);
SELECT MYCAT_SEQ_SETVAL('GLOBAL', 1);
SELECT MYCAT_SEQ_CURRVAL('GLOBAL');
SELECT MYCAT_SEQ_NEXTVAL('GLOBAL'); 
4.插入序列数据：
INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 0, 100); 
INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 0, 100);

脚本下载：/attached/file/20150323/20150323175900_382.txt

说明：

• 在某个分区(dataNode)数据库上创建序列号相关的表格和函数，SQL脚本在doc目录下的sequnce-sql.txt中，需要在数据库上而非Mycat上执行。
• Mycat_home/conf/quence_db_conf.properties 中记录了sequnce所存放的db对应的配置信息。
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
• 在sequnce表中，插入相应的sequnce记录，并确定其初始值，以及增长步长，步长建议一个合适的范围，比如50-500，需要在数据库上而非Mycat上执行。
INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 0, 100);
• 修改sequnce的当前值为某个新值，需要在数据库上而非Mycat上执行。
SELECT mycat_seq_curval('GLOBAL');

提示：步长选择多大，取决与你数据插入的TPS，假如是每秒1000个，则步长为1000×60=6万，也不是很大，即60秒会重新从数据库读取下一批次的序列号值。

测试：

3. 本地时间戳算法：

ID= 64位二进制 (42(毫秒)+5(机器ID)+5(业务编码)+12(重复累加)

换算成十进制为18位数的long类型，每毫秒可以并发12位二进制的累加。

使用方式：

a. 配置server.xml

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

b. 在mycat下配置：sequence_time_conf.properties

WORKID=0-31 任意整数

DATAACENTERID=0-31 任意整数

多个个mycat节点下每个mycat配置的 WORKID，DATAACENTERID不同，组成唯一标识，总共支持32*32=1024种组合。

ID示例：56763083475511

自增主键配置：

从MyCAT 1.3开始，支持自增长主键，依赖于全局序列号机制，建议采用数据库方式的全局序列号，并正确设置步长，以免影响实际性能。

首先要开启数据库方式的全局序列号，对于需要定义自增长主键的表，建立对应的全局序列号，与table名称同名大写，

如customer序列名为CUSTOMER，然后再 schema.xml 中对customer表的table元素增加属性autoIncrement值为true.

执行insert into customer (name,company_id,sharding_id) values ('test',2,10000);查看效果，

暂不支持主键为null如：insert into customer (id,name,company_id,sharding_id) values (null,'test',2,10000);

应用获得自增主键：

MyCAT自增字段和返回生成的主键ID的经验分享
说明：
1、mysql本身对非自增长主键，使用last_insert_id()是不会返回结果的，只会返回0.
2、mysql只会对定义自增长主键，可以用last_insert_id()返回主键值。

mycat目前提供了自增长主键功能，但是如果对应的mysql节点上数据表，没有定义auto_increment，
那么在mycat层调用last_insert_id()也是不会返回结果的。
正确使用方式如下：

1、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;
2、mycat定义自增
[root@test conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

        <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>

        <dataNode name="dn1" dataHost="localhost1" database="db1" />
        <dataNode name="dn2" dataHost="localhost1" database="db2" />
        <dataNode name="dn3" dataHost="localhost1" database="db3" />
        <dataNode name="dn4" dataHost="localhost1" database="db4" />
        <dataNode name="dn5" dataHost="localhost1" database="db5" />

        <dataHost name="localhost1" maxCon="1000" minCon="20" balance="0" writeType="0" dbType="mysql" dbDriver="native">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="127.0.0.1:3366" user="root" password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

3、mycat对应sequence_db_conf.properties增加相应设置；
4、mycat的对应mycat_sequence增加对应记录。
5、链接mycat，测试结果如下：
 
127.0.0.1/root:[TESTDB> insert into tt2(nm) values (99);
Query OK, 1 row affected (0.14 sec)

127.0.0.1/root:[TESTDB> select last_insert_id();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              101 |
+------------------+
1 row in set (0.01 sec)

127.0.0.1/root:[TESTDB> insert into tt2(nm) values (99);
Query OK, 1 row affected (0.00 sec)

127.0.0.1/root:[TESTDB> select last_insert_id();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              102 |
+------------------+
1 row in set (0.00 sec)

127.0.0.1/root:[TESTDB> insert into tt2(nm) values (99);
Query OK, 1 row affected (0.00 sec)

127.0.0.1/root:[TESTDB> select last_insert_id();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              103 |
+------------------+
1 row in set (0.00 sec)

127.0.0.1/root:[TESTDB> insert into tt2(nm) values (99);
Query OK, 1 row affected (0.01 sec)

127.0.0.1/root:[TESTDB> select last_insert_id();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              104 |
+------------------+
1 row in set (0.00 sec)

127.0.0.1/root:[TESTDB> insert into tt2(nm) values (99);
Query OK, 1 row affected (0.00 sec)

127.0.0.1/root:[TESTDB> select last_insert_id();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              105 |
+------------------+
1 row in set (0.00 sec)

127.0.0.1/root:[TESTDB> insert into tt2(nm) values (99);
Query OK, 1 row affected (0.00 sec)

127.0.0.1/root:[TESTDB> select last_insert_id();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              106 |
+------------------+
1 row in set (0.00 sec)

关于批量插入使用：

如果没有用mycat 的全局序列号，是普通的批量插入：

insert（a,b,c） values(x,x,x),(x,x,x);

如果用了全局序列号必须加注解：

/*!mycat:catlet=demo.catlets.BatchInsertSequence */insert（a,b,c） values(x,x,x),(x,x,x);

是sharding key 必须包含在列枚举中，特别是主键是自增的时候必须显示调用：

/*!mycat:catlet=demo.catlets.BatchInsertSequence */insert（id,a,b,c） values(,next value for MYCATSEQ_ID,x,x,x),(next value for MYCATSEQ_ID,x,x,x);

从零开始

世界上有不绝的风景，我有不老的心情。

mycat分布式mysql中间件（自增主键）

mycat分布式mysql中间件（自增主键）

取消回复