1.7.1 MySQL-offset-step 方式

1.7.1.1 MySQL-offset-step序列配置

mysql序列由文件sequence_db_conf.properties进行配置。具体配置有如下格式:

#this is comment

`schema1`.`table1`=node1

`schema1`.`table2`=node1

`schema2`.`table1`=node2

...

schemaX：使用全局序列的dble表所属的dble库名。

tableX：使用全局序列dble的dble表名。

nodeX：实现序列功能的数据节点名。此节点具体配置见下节。

1.7.1.2 数据节点配置

mysql序列的实现依赖一些存储函数。因此在序列可用之前必须在数据节点nodeX上创建这些存储函数，并针对使用此节点的表（`schemaX`.`tableX`)初始化序列初始值。

数据节点nodeX上创建必要的表和存储函数

具体步骤：

a. 用mysql客户端登录到nodeX上

mysql ...

b. 切换到nodeX上的后端存储数据库db(参见1.5 sharding.xml)

use db;

c. 执行创建脚本dbsql.sql（dbseq.sql的内容详见1.7.1.3节）

source .../dbseq.sql;

d. 初始化相应序列初始值

INSERT INTO DBLE_SEQUENCE VALUES ('`schemaX`.`tableX`', 1, 1);

...

1.7.1.3 dbseq.sql内容

dbseq.sql的内容如下：


DROP TABLE IF EXISTS DBLE_SEQUENCE;  
CREATE TABLE DBLE_SEQUENCE ( name VARCHAR(64) NOT NULL, current_value BIGINT(20) NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB;

-- ----------------------------  
-- Function structure for `dble_seq_currval`  
-- ----------------------------  
DROP FUNCTION IF EXISTS `dble_seq_currval`;  
DELIMITER ;;  
CREATE FUNCTION `dble_seq_currval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1  
DETERMINISTIC  
BEGIN  
DECLARE retval VARCHAR(64);  
SET retval="-1,0";  
SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval FROM DBLE_SEQUENCE WHERE name = seq_name;  
RETURN retval ;  
END  
;;  
DELIMITER ;

-- ----------------------------  
-- Function structure for `dble_seq_nextval`  
-- ----------------------------  
DROP FUNCTION IF EXISTS `dble_seq_nextval`;  
DELIMITER ;;  
CREATE FUNCTION `dble_seq_nextval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1  
DETERMINISTIC  
BEGIN  
DECLARE retval VARCHAR(64);  
DECLARE val BIGINT;  
DECLARE inc INT;  
DECLARE seq_lock INT;  
set val = -1;  
set inc = 0;  
SET seq_lock = -1;  
SELECT GET_LOCK(seq_name, 15) into seq_lock;  
if seq_lock = 1 then  
SELECT current_value + increment, increment INTO val, inc FROM DBLE_SEQUENCE WHERE name = seq_name for update;  
if val != -1 then  
UPDATE DBLE_SEQUENCE SET current_value = val WHERE name = seq_name;  
end if;  
SELECT RELEASE_LOCK(seq_name) into seq_lock;  
end if;  
SELECT concat(CAST((val - inc + 1) as CHAR),",",CAST(inc as CHAR)) INTO retval;  
RETURN retval;  
END  
;;  
DELIMITER ;

-- ----------------------------  
-- Function structure for `dble_seq_setvals`  
-- ----------------------------  
DROP FUNCTION IF EXISTS `dble_seq_nextvals`;  
DELIMITER ;;  
CREATE FUNCTION `dble_seq_nextvals`(seq_name VARCHAR(64), count INT) RETURNS VARCHAR(64) CHARSET latin1  
DETERMINISTIC  
BEGIN  
DECLARE retval VARCHAR(64);  
DECLARE val BIGINT;  
DECLARE seq_lock INT;  
SET val = -1;  
SET seq_lock = -1;  
SELECT GET_LOCK(seq_name, 15) into seq_lock;  
if seq_lock = 1 then  
SELECT current_value + count INTO val FROM DBLE_SEQUENCE WHERE name = seq_name for update;  
IF val != -1 THEN  
UPDATE DBLE_SEQUENCE SET current_value = val WHERE name = seq_name;  
END IF;  
SELECT RELEASE_LOCK(seq_name) into seq_lock;  
end if;  
SELECT CONCAT(CAST((val - count + 1) as CHAR), ",", CAST(count as CHAR)) INTO retval;  
RETURN retval;  
END  
;;  
DELIMITER ;

-- ----------------------------  
-- Function structure for `dble_seq_setval`  
-- ----------------------------  
DROP FUNCTION IF EXISTS `dble_seq_setval`;  
DELIMITER ;;  
CREATE FUNCTION `dble_seq_setval`(seq_name VARCHAR(64), value BIGINT) RETURNS varchar(64) CHARSET latin1  
DETERMINISTIC  
BEGIN  
DECLARE retval VARCHAR(64);  
DECLARE inc INT;  
SET inc = 0;  
SELECT increment INTO inc FROM DBLE_SEQUENCE WHERE name = seq_name;  
UPDATE DBLE_SEQUENCE SET current_value = value WHERE name = seq_name;  
SELECT concat(CAST(value as CHAR),",",CAST(inc as CHAR)) INTO retval;  
RETURN retval;  
END  
;;  
DELIMITER ;

1.7.1 MySQL offset-step方式