序列号（sequence）

配置序列号

使用序列号的分片表,对应的自增主键要在建表SQL中体现
例如
id bigint NOT NULL AUTO_INCREMENT

2021-8-17后支持autoIncrement属性强制关闭全局序列号

        "xxx":{
            "createTableSQL":"xxx",
            "autoIncrement": false,
            "function":{
                "clazz":"io.mycat.router.mycat1xfunction.PartitionByMod",
                "name":"PartitionByMod",
                "properties":{
                    "defaultNode":"0",
                    "type":"Integer",
                    "count":4,
                    "columnName":"id"
                },
                "ranges":{}
            },
            "partition":{
                "schemaNames":"xxxx",
                "tableNames":"xxxx_$0-3",
                "targetNames":"prototype"
            }
        },

如果不需要使用mycat的自增序列,而使用mysql本身的自增主键的功能,需要在配置中更改对应的建表sql,不设置AUTO_INCREMENT关键字,这样,mycat就不认为这个表有自增主键的功能,就不会使用mycat的全局序列号.
这样,对应的插入sql在mysql处理,由mysql的自增主键功能补全自增值.

建表sql可以自动在原型库对应的逻辑表的物理表获取,如果逻辑表的建表SQL与物理表的建表SQL不对应,则需要在配置文件中配置建表SQL.

{数据库名字}_{表名字}.sequence.json
放置在sequecnes文件夹下面

{
    "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
    "name":"db1_travelrecord"
}

如果不配置,分片表使用雪花算法生成全局序列号

如果部署多个Mycat,请修改server.json的mycatId参数,使雪花序号严格唯一

io.mycat.plug.sequence.SequenceMySQLGenerator

{
    "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
    "name":"db1_travelrecord"
}

在prototype服务器的db1库导入dbseq.sql文件
在mycat_sequence表建立db1_travelrecord行,即可,Mycat会从更新此行获得序列号
注意库名对应这里db1_travelrecord中的db1就是dbseq.sql导入的库(物理库,一般是原型库,不能在mycat里执行)
可选参数targetName 更改序列号服务器

{
    "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
    "name":"db1_travelrecord",
  "targetName": "prototype",
  "schemaName":"db1"//指定物理库名
}

“targetName”: “prototype” 是执行自增序列的节点,也是dbseq.sql导入的节点
dbseq.sql导入的当前库的库名与逻辑表的逻辑库名一致
导入后检查库下有没有mycat_sequence表

其中increment是序列号自增的步伐,为1的时候严格按1递增,当1000的时候,mycat会每次批量递增1000取序列号.此时在多个mycat访问此序列号表的情况下,不能严格自增

NAME列中的值是对应的 库名_表名 该值需要用户设置,即插入一条逻辑表相关的记录,用于记录序列号

通过注解设置为雪花算法

/*+ mycat:setSequence{"name":"db1_travelrecord","time":true} */;

使用注释前要导入dbseq.sql以及设置mycat_sequence表内的逻辑表记录

通过注释设置为数据库方式全局序列号

/*+ mycat:setSequence{"name":"db1_travelrecord","clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator"} */;

参数与上述的数据库方式相同

/*+ mycat:setSequence{
"name":"db1_travelrecord",
"clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
"name":"db1_travelrecord",
  "targetName": "prototype",
  "schemaName":"db2"
  } */;

dbseq.sql

DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_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 `mycat_seq_currval`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE FUNCTION `mycat_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 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 FUNCTION `mycat_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 MYCAT_SEQUENCE WHERE name = seq_name for update;
      if val != -1 then
          UPDATE MYCAT_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 `mycat_seq_setvals`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_nextvals`;
DELIMITER ;;
CREATE FUNCTION `mycat_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 MYCAT_SEQUENCE WHERE name = seq_name for update;
        IF val != -1 THEN
            UPDATE MYCAT_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(val as CHAR)) INTO retval;
    RETURN retval;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for `mycat_seq_setval`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE FUNCTION `mycat_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 MYCAT_SEQUENCE WHERE name = seq_name;
    UPDATE MYCAT_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 ;

INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 1, 1);

mysql的sql_mode 的NO_AUTO_VALUE_ON_ZERO会影响mysql的自增序列号的行为