【Mycat】主键id自增长配置

最新推荐文章于 2022-04-01 14:24:12 发布

cml2016

最新推荐文章于 2022-04-01 14:24:12 发布

阅读量357

点赞数

文章标签：数据库

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

1.看下配置文件如下：

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                
                <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

                <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
                   <table name="employee" primaryKey="ID" dataNode="dn1,dn2"

rule="sharding-by-intfile" />

配置Server.xml

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

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

在121（dn1）上创建
1 创建sequence表

CREATE TABLE `MYCAT_SEQUENCE` (
  `name` varchar(50) NOT NULL,
  `current_value` int(11) NOT NULL,
  `increment` int(11) NOT NULL DEFAULT '100',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB ====这是必备的三个字段，还可以加些别的字段

2 获取下一个值

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获取当前值

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 ;

4 设置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 ;　

5.插入初始数据

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 1, 10); INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('TRAVELRECORD', 1, 10); INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('EMPLOYEE', 1, 10);

6.设置 sequence_db_conf.properties

[root@localhost conf]# more sequence_db_conf.properties
#sequence stored in datanode
GLOBAL=dn1 COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
EMPLOYEE=dn1
TRAVELRECORD=dn1 ----需要配置在该文件中并指定数据节点，一定要大写 TT2=dn1

7.重启mycat 或reload

3 测试

全局表唯一表： mysql> insert into company(name) values ('auto_test');
Query OK, 1 row affected (1.08 sec)

mysql> insert into company(name) values ('auto_test');
Query OK, 1 row affected (0.45 sec)

mysql> select * from company;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | this is 119:3307 |
| 9 | this is writeeeeee |
| 10 | auto test |
| 11 | auto test |
| 12 | auto test |
| 13 | auto test |
| 14 | auto test |
| 15 | terwrw |
| 16 | auto_test |
| 17 | auto_test |
+----+--------------------+
10 rows in set (0.16 sec)
####全局表和唯一表是可以直接插入的，不需要指定id，和单实例无差异

分片表： mysql> show create table employee\G;
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sharding_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
1 row in set (0.19 sec)

mysql> insert into employee(id,name,sharding_id) values (next value for MYCATSEQ_EMPLOYEE,'zs2',10000); --插入第一条后
Query OK, 1 row affected (0.00 sec)

mysql> select * from MYCAT_SEQUENCE; ----起始值变为11，此时第一条id也为11=1+10
+---------------+---------------+-----------+
| name | current_value | increment |
+---------------+---------------+-----------+
| EMPLOYEE | 11 | 10 |
| GLOBAL | 1 | 10 |
| TRAVELRECORD | 1 | 10 |
| TT2 | 30 | 10 |
+---------------+---------------+-----------+

再连续插入9条，上面的数据不变，当插入到第10条时，即自增id>11+10 时，上面会变成
mysql> select * from MYCAT_SEQUENCE;
+---------------+---------------+-----------+
| name | current_value | increment |
+---------------+---------------+-----------+
| EMPLOYEE | 21 | 10 |
| GLOBAL | 1 | 10 |
| TRAVELRECORD | 1 | 10 |
| TT2 | 30 | 10 |
+---------------+---------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from employee;
+----+------+-------------+
| id | name | sharding_id |
+----+------+-------------+
| 11 | zs2 | 10000 |
| 12 | zs2 | 10000 |
| 13 | zs2 | 10000 |
| 14 | zs2 | 10000 |
| 15 | zs2 | 10000 |
| 16 | zs2 | 10000 |
| 17 | zs2 | 10000 |
| 18 | zs2 | 10000 |
| 19 | zs2 | 10000 |
| 20 | zs2 | 10000 |
| 21 | zs2 | 10000 |
| 22 | zs2 | 10000 |
| 23 | zs2 | 10000 |
+----+------+-------------+
13 rows in set (0.00 sec)

如果自增id不是分表的依据字段，也可以以这种方式插入
mysql> insert into employee(name,sharding_id) values ('zs2',10000);
Query OK, 1 row affected (0.00 sec)
但是这种方式会出现问题：

mysql> insert into employee(name,sharding_id) values ('123',10000),('123',10000),('123',10010),('123',10010);
Query OK, 4 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from employee;
+----+------+-------------+
| id | name | sharding_id |
+----+------+-------------+
| 1 | 123 | 10010 |
| 2 | 123 | 10010 | --id在各自的分片上不冲突，但是合在一起会有重复，自增id不是全局的
| 1 | 123 | 10000 |
| 2 | 123 | 10000 |
+----+------+-------------+

解决办法：不要忽略主键id，使用next value for MYCATSEQ_EMPLOYEE替

如果自增id是分片表的依据字段，那也是不能省略的：
mysql> insert into travelrecord(name) values ('auto_test');
ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO travelrecord (name)
VALUES ('auto_test')

但是在程序层不可能使用next value for MYCATSEQ_EMPLOYEE 或者分片表每次都指定id吧，那就把自增id配成全局的！如下：

<table name="travelrecord" primaryKey="ID" dataNode="dn1,dn2,dn3" autoIncrement="true" rule="auto-sharding-long" />
<table name="tt2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" /> autoIncrement="true" rule="sharding-by-intfile" /> ###增加autoincrement属性

测试：
mysql> insert into travelrecord(name) values ('123'),('123'),('123');
Query OK, 1 row affected (0.63 sec)

mysql> insert into employee(name,sharding_id) values ('123',10000),('123',10000),('123',10010),('123',10010);
Query OK, 1 row affected (0.12 sec)

mysql> insert into tt2(nm) values (99),(100),(101),(102);

mysql> select * from employee;
+----+------+-------------+
| id | name | sharding_id |
+----+------+-------------+
| 91 | 123 | 10000 |
| 92 | 123 | 10000 |
| 93 | 123 | 10010 |
| 94 | 123 | 10010 |
+----+------+-------------+
4 rows in set (0.01 sec)

mysql> select * from travelrecord;
+----+------+
| id | name |
+----+------+
| 51 | 123 |
| 52 | 123 |
| 53 | 123 |
+----+------+
3 rows in set (0.01 sec)

mysql> select * from tt2;
+----+-----+
| id | nm |
+----+-----+
| 30 | 99 |
| 33 | 102 |
| 31 | 100 |
| 32 | 101 |
+----+-----+
4 rows in set (0.38 sec)

注意：mycat_sequence知识帮你获取非相同的主键id，如果你非要插入一个相同的主键id，但是处于不同的分片上，那是可以的！如:

mysql> insert into employee(id,name,sharding_id) values (91,'123',10010);
Query OK, 1 row affected (0.12 sec)

mysql> select * from employee;
+----+------+-------------+
| id | name | sharding_id |
+----+------+-------------+
| 91 | 123 | 10000 |
| 92 | 123 | 10000 |
| 93 | 123 | 10010 |
| 94 | 123 | 10010 |
| 91 | 123 | 10010 |
+----+------+-------------+
4 rows in set (0.01 sec)

总结如下:
最开始的id=current_value+increment，继续插入时如果id一直<第二次的current_value+increment，current_value不会变，直到自增id 超过，然后current_value变为current_value+increment，以此类推

参考：
http://www.cnblogs.com/raphael5200/p/5900007.html

https://github.com/yujiahui/Mycat-doc/blob/master/MyCAT自增字段和返回生成的主键ID的经验分享

<table name="employee" primarykey="ID" datanode="dn1,dn2"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-2137902/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-2137902/

cml2016

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【Mycat】主键id自增长配置

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

扫一扫