mysql 存储过程主键自增长_MyCat 主键ID自增长配置

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，MyCat 提供了全局sequence，并且提供了包含本地配置和数据库配置等多种实现方式，实现方式主要有三种：本地文件方式、数据库方式、本地时间戳算法。

一、本地文件方式

原理：此方式MyCAT将sequence配置到文件中，当使用到sequence中的配置后，MyCAT会更下conf中的sequence_conf.properties文件中sequence当前的值。

譬如：

#default global sequence

GLOBAL.HISIDS=

GLOBAL.MINID=10001

GLOBAL.MAXID=20000

GLOBAL.CURID=10000

#其中HISIDS表示使用过的历史分段(一般无特殊需要可不配置)，MINID表示最小ID值，MAXID表示最大ID值，CURID表示当前ID值。

使用方式：

1、配置MyCat的Server.xml

# 其中0，表示使用本地文件方式。

0

2、配置sequence_conf.properties

$ vim mycat/conf/sequence_conf.properties

#default global sequence

GLOBAL.HISIDS=

GLOBAL.MINID=10001

GLOBAL.MAXID=20000

GLOBAL.CURID=10000

设置完成以后重启MyCat

3、测试

mysql> create table test(id int,name varchar(20));

mysql> insert into test(id,name) values(next value for MYCATSEQ_GLOBAL,@@hostname);

mysql> select * from test;

#此时，sequence_conf.properties中GLOBAL.CURID值为10001。当然，可以使用sequence_conf.properties中定义的任何规则，譬如：

# self define sequence

COMPANY.HISIDS=

COMPANY.MINID=1001

COMPANY.MAXID=2000

COMPANY.CURID=1000

# COMPANY 就是要使用自增的配置，在这里也可以使用其他的名字，但必须是大写的；定义以后可以在全局使用。

#可以使用 mysql>select next value for MYCATSEQ_xxx(自定义的名字); 来查看下一个自增ID。

二、数据库方式

原理：在数据库中建立一张表，存放sequence名称(name)，sequence当前值(current_value)，步长(increment int类型每次读取多少个sequence，假设为K)等信息；

Sequence获取步骤：

1).第一次使用该sequence时，根据传入的sequence名称，从数据库这张表中读取current_value，和increment到MyCat中，并将数据库中的current_value设置为原current_value值+increment值(实现方式是基于后续的存储函数)

2).MyCat将读取到current_value+increment作为本次要使用的sequence值，下次使用时，自动加1，当使用increment次后，执行步骤1)相同的操作.

MyCat负责维护这张表，用到哪些sequence，只需要在这张表中插入一条记录即可。若某次读取的sequence没有用完，系统就停掉了，则这次读取的sequence剩余值不会再使用。

使用方式：

1、配置Server.xml

# 其中1，表示使用数据库方式。

1

2、在其中一个分片点对应的数据库中创建表和存储过程

因我在schema.xml 中配置的是： 

譬如我在dn2中创建，对应的数据库名为db2(为什么这里会涉及到datanode，因为后续的sequence_db_conf.properties文件会使用到)，

注意，是登录到数据库中创建，

而不是在mycat中创建。

--  创建MYCAT_SEQUENCE表

CREATE TABLE MYCAT_SEQUENCE (

`name` VARCHAR(50) NOT NULL,

current_value INT NOT NULL,

increment INT NOT NULL DEFAULT 1,

remark varchar(100), -- remark 并不是必须的，在这里我是为了让每一个表都对应一个全局的自增，在Remark中配置自增项对应的表名。方便后期维护

PRIMARY KEY(name)) ENGINE=InnoDB;

-- – 获取当前sequence的值(返回当前值,增量)

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 ;

-- 设置sequence值

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 ;

-- 获取下一个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 latin1

DETERMINISTIC

BEGIN

UPDATE MYCAT_SEQUENCE

SET current_value = value

WHERE name = seq_name;

RETURN mycat_seq_currval(seq_name);

END

;;

DELIMITER ;

在表MYCAT_SEQUENCE中，其中：

– name sequence名称

– current_value 当前value

– increment增长步长! 可理解为mycat在数据库中一次读取多少个sequence. 当这些用完后, 下次再从数据库中读取.

注意：MYCAT_SEQUENCE必须大写。

创建存储函数：

注意：必须在同一个数据库中创建，在本例中，是db2。一共要创建三个。

– 获取当前sequence的值(返回当前值,增量)

3、  插入sequence记录

-- 插入sequence记录

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment,remark) VALUES ('DICT', 1, 100,'match:tb_dic');

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

-- 代表插入了一个名为mycat的sequence，当前值为1，步长为100。

mysql> select * from mycat_sequence;

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

| name | current_value | increment | remark |

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

| DICT | 1 | 100 | match:tb_dic |

| GLOBAL | 200 | 100 | GLOBAL |

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

至此，数据库方面的准备工作已结束完毕。

4、设置 sequence_db_conf.properties

在mycat conf目录下的sequence_db_conf.properties文件中添加如下内容：DICT=dn3 dn3:表示我把表和函数都建在了dn3节点上

注意：DICT必须为大写，这个与表的数据是否大写无关，事实上，MYCAT_SEQUENCE中name是否大小写对结果没有影响。

重启MyCat

5、开始测试

$ mysql -h127.0.0.1 -utest -ptest -P8066 -DTESTDB

mysql>

create table tb_dic

(

id int not null auto_increment,

dic_name varchar(100) not null comment '字典名称',

dic_value varchar(20) not null comment '字典值',

dic_type int not null comment '字典类型： 如果是类型，如支付方式等',

primary key (id)

);

# 然后插入值

mysql> INSERT into tb_dic(id,dic_name,dic_value,dic_type) VALUES(next value for MYCATSEQ_DICT,'支付方式','1',0);

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

| id | dic_name | dic_value | dic_type |

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

| 101 | 活动形式 | 2 | 0 |

| 102 | 表单类型 | 2 | 0 |

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

错误处理：

ERROR 1003 (HY000): mycat sequnce err.org.opencloudb.config.util.ConfigException: can't find definition for sequence :DICT

因为对于sequence_db_conf.properties的修改当前的mycat并不知晓，这时候，可重启mycat或者登录9066管理端口进行 reload @@config;

至此，测试完毕，关键还是两点：MYCAT_SEQUENCE必须大写，sequence_db_conf.properties文件中DICT=dn3必须大写。

三、本地时间戳算法

待续。。。