MyCat自增主鍵

全局序列號是MyCAT提供的一個新功能，為了實(shí)現(xiàn)分庫分表情況下，表的主鍵是全局唯一，而默認(rèn)的MySQL的自增長主鍵無法滿足這個要求。全局序列號的語法符合標(biāo)準(zhǔn)SQL規(guī)范，其格式為：

創(chuàng)新互聯(lián)主要從事成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、網(wǎng)頁設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)青浦,十多年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):028-86922220

next value for MYCATSEQ_XXX

MYCATSEQ_XXX是序列號的名字，MyCAT自動創(chuàng)建新的序列號，免去了開發(fā)的復(fù)雜度。

另外，MyCAT也提供了一個全局的序列號，名稱為：MYCATSEQ_GLOBAL

注意，MYCATSEQ_必須大寫才能正確識別。

MyCAT溫馨提示：實(shí)踐中，建議每個表用自己的序列號，序列號的命名建議為MYCATSEQ _tableName_ID_SEQ。

實(shí)現(xiàn)方式主要有三種：本地文件方式、數(shù)據(jù)庫方式、本地時間戳算法。

一、本地文件方式：

1、原理：此方式MyCAT將sequence配置到文件中，當(dāng)使用到sequence中的配置后，MyCAT會更新conf中的sequence_conf.properties文件中sequence當(dāng)前的值。

2、使用方式：

（1）、配置MyCat的Server.xml

#其中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

# self define sequence自定義

COMPANY.HISIDS=

COMPANY.MINID=1001

COMPANY.MAXID=2000

COMPANY.CURID=1000

#其中HISIDS表示使用過的歷史分段(一般無特殊需要可不配置)，MINID表示最小ID值，MAXID表示最大ID值，CURID表示當(dāng)前ID值。

# GLOBAL在這里也可以使用其他的名字，但必須是大寫的；定義以后可以在全局使用。

#可以使用 mysql>select next value for MYCATSEQ_xxx(自定義的名字，這里就是MYCATSEQ_ GLOBAL); 來查看下一個自增ID。

設(shè)置完成以后重啟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;

4、優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：本地加載，讀取速度較快。

缺點(diǎn)：當(dāng) MyCAT 重新發(fā)布后，配置文件中的 sequence 會恢復(fù)到初始值。

二、數(shù)據(jù)庫方式：

1、原理：

在數(shù)據(jù)庫中建立一張表，存放sequence名稱(name)，sequence當(dāng)前值(current_value)，步長(increment int類型每次讀取多少個sequence，當(dāng)讀取的increment用完后，

再次讀取increment提高效率，顯然mycat的increment和mysql的increment意義不同)等信息.

2、Sequence獲取步驟：

（1）、第一次使用該sequence時，根據(jù)傳入的sequence名稱，從數(shù)據(jù)庫這張表中讀取current_value，和increment到MyCat中，并將數(shù)據(jù)庫中的current_value設(shè)置為原current_value值+increment值（實(shí)現(xiàn)方式是基于后續(xù)的存儲函數(shù)）。

（2）、MyCat將讀取到current_value+increment作為本次要使用的sequence值，下次使用時，自動加1，當(dāng)使用increment次后，執(zhí)行步驟1)相同的操作.　MyCat負(fù)責(zé)維護(hù)這張表，用到哪些sequence，只需要在這張表中插入一條記錄即可。若某次讀取的sequence沒有用完，系統(tǒng)就停掉了，則這次讀取的sequence剩余值不會再使用。

3、使用方式：

（1）、配置Server.xml ，其中1，表示使用數(shù)據(jù)庫方式。

（2）、設(shè)置 sequence_db_conf.properties

在mycat conf目錄下的sequence_db_conf.properties文件中添加如下內(nèi)容：

#sequence stored in datanode

GLOBAL=dn2

DICT=dn2

dn2:表示把表和函數(shù)都建在了dn2節(jié)點(diǎn)上。

注意：GLOBAL 和DICT必須為大寫。

重啟MyCat

（3）、在其中一個分片點(diǎn)對應(yīng)的數(shù)據(jù)庫中創(chuàng)建表和存儲函數(shù)

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

譬如我在dn2中創(chuàng)建，對應(yīng)的數(shù)據(jù)庫名為db2（為什么這里會涉及到datanode，因?yàn)楹罄m(xù)的sequence_db_conf.properties文件會使用到）。

注意，登錄到數(shù)據(jù)庫中創(chuàng)建表和存儲函數(shù)，而不是在mycat中創(chuàng)建?！　　?/p>

（3.1）、創(chuàng)建表-- 創(chuàng)建MYCAT_SEQUENCE表

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

remark varchar(100), -- remark并不是必須的，在這里是為了讓每一個表都對應(yīng)一個全局的自增，在Remark中配置自增項(xiàng)對應(yīng)的表名。方便后期維護(hù)

PRIMARY KEY(name)) ENGINE=InnoDB;

（3.2）、創(chuàng)建存儲函數(shù)1--– 獲取當(dāng)前sequence的值（返回當(dāng)前值,增量）

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 ;

（3.3）、創(chuàng)建存儲函數(shù)2-- 獲取下一個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 ;

（3.4）、創(chuàng)建存儲函數(shù)3--設(shè)置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 當(dāng)前value

–increment增長步長! 可理解為mycat在數(shù)據(jù)庫中一次讀取多少個sequence. 當(dāng)這些用完后, 下次再從數(shù)據(jù)庫中讀取.

注意：MYCAT_SEQUENCE必須大寫。

創(chuàng)建存儲函數(shù)：

注意：必須在同一個數(shù)據(jù)庫中創(chuàng)建，在本例中，是db2。一共要創(chuàng)建三個存儲函數(shù)。

–獲取當(dāng)前sequence的值（返回當(dāng)前值,增量）。

4、插入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，當(dāng)前值為1，步長為100。

mysql> select * from mycat_sequence;

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

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

| DICT | 1 | 100 | match:tb_dic |

| GLOBAL | 200 | 100 | GLOBAL |

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

至此，數(shù)據(jù)庫方面的準(zhǔn)備工作已結(jié)束完畢。

5、開始測試

$ mysql -h227.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);

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

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

| 101 |活動形式 | 2 | 0 |

| 102 |表單類型 | 2 | 0 |

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

錯誤處理：　

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

因?yàn)閷τ趕equence_db_conf.properties的修改當(dāng)前的mycat并不知曉，這時候，可重啟mycat或者登錄9066管理端口進(jìn)行 reload @@config ;

至此，測試完畢，關(guān)鍵還是兩點(diǎn)：MYCAT_SEQUENCE必須大寫，sequence_db_conf.properties文件中DICT=dn2必須大寫。

三、本地時間戳算法

ID= 64位二進(jìn)制 (42(毫秒)+5(機(jī)器ID)+5(業(yè)務(wù)編碼)+12(重復(fù)累加) 。

換算成十進(jìn)制為18位數(shù)的long類型，每毫秒可以并發(fā)12位二進(jìn)制的累加。

1、配置server.xml

2、在mycat下配置：sequence_time_conf.properties

WORKID=0-31任意整數(shù)

DATAACENTERID=0-31任意整數(shù)

每個mycat配置的 WORKID，DATAACENTERID不同，組成唯一標(biāo)識，總共支持32*32=1024種組合。

ID示例：56763083475511

四、總結(jié)：

1、從MyCAT 1.3開始，支持自增長主鍵，依賴于全局序列號機(jī)制，建議采用數(shù)據(jù)庫方式的全局序列號，并正確設(shè)置步長，以免影響實(shí)際性能。

首先要開啟數(shù)據(jù)庫方式的全局序列號，對于需要定義自增長主鍵的表，建立對應(yīng)的全局序列號，與table名稱同名大寫，

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

2、應(yīng)用如何獲得自增主鍵：

MyCAT自增字段和返回生成的主鍵ID的經(jīng)驗(yàn)分享

（1）、mysql本身對非自增長主鍵,使用last_insert_id()不會返回結(jié)果,只會返回0.

（2）、mysql只會對定義自增長主鍵，可以用last_insert_id()返回主鍵值。

（3）、mycat目前提供了自增長主鍵功能，但是如果對應(yīng)的mysql節(jié)點(diǎn)上數(shù)據(jù)表，沒有定義auto_increment，那么在mycat層調(diào)用last_insert_id()也是不會返回結(jié)果的。

（4）、數(shù)據(jù)庫方式正確的使用方式如下：

（4.1）、mysql定義自增主鍵
CREATE TABLE `tt2` (
`id` bigINT(10) UNSIGNED NOT NULL AUTO_INCREMENT, //必須是自增的
`nm` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

（4.2）、mycat定義自增

[root@test conf]# vim schema.xml

select user()

（4.3）、mycat對應(yīng)sequence_db_conf.properties增加相應(yīng)設(shè)置；并在mycat的對應(yīng)mycat_sequence增加對應(yīng)記錄。

（4.4）、連接mycat，測試結(jié)果如下：

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 |