mycat(1)主键自增之数据库自增的方式

数据准备

1、创建数据库

创建两个数据库，并都创建test表

#创建数据库
create database mycat;

#添加表
use mycat 
#创建表test
create table test3(id int auto_increment primary key,name varchar(32)) ;

#创建数据库
create database mycat3;

#添加表
use mycat 2
#创建表test
create table test3(id int auto_increment primary key,name varchar(32)) ;123456789101112131415

添加后的表数据，看了一下，表示表建立好了

2、创建Mycat生成唯一主键所需要的函数

我将自增长的函数都创建在了mycat这个db中

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 100,
PRIMARY KEY (NAME)
) ENGINE = INNODB ;


INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('GLOBAL', 100000, 100);

DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) 
RETURNS VARCHAR(64) CHARSET utf8
    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 ;

DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
 CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE  
                 SET current_value = current_value + increment 
                  WHERE NAME = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;


DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), VALUE INTEGER) 
RETURNS VARCHAR(64) CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE  
                   SET current_value = VALUE  
                   WHERE NAME = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152

3、测试添加结果

#查询当前所在db ,这个语句只是想给大家证明，我是在mycat 这个数据库里面添加的函数
select database();

#查询doubi这张表里面的数据
select mycat_seq_nextval('doubi');12345

4、插入要自增的表名称到MYCAT_SEQUENC表

插入数据的时候，一定要注意，自己需要插入的表，一定要是大写的

#设定TEST3表的增长方式为 步进为1 ，
insert into MYCAT_SEQUENCE (name,current_value,increment) values ('TEST3',0,1);

#查询结果
 select * from MYCAT_SEQUENCE;12345

配置maycat

1、配置schemal.xml

vim conf/schemal.xml， 数据库的表名称为test2，设定表的主键，这样mycat好给我们生成主键，而且我们是自增长，所以需要配置autoIncrement="true"，这个里面 type="global" 表示是全局的表， 每个子节点，都会存在

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="yellowcong" checkSQLschema="true" sqlMaxLimit="1000">
                <table name="test3" dataNode="jdbc_node1,jdbc_node2" primaryKey="id" autoIncrement="true"  type="global"/>
        </schema>

        <dataNode name="jdbc_node1" dataHost="localhost" database="mycat" />
        <dataNode name="jdbc_node2" dataHost="localhost" database="mycat2" />

        <dataHost name="localhost" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>

                <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root" />

        </dataHost>


</mycat:schema>

1234567891011121314151617181920212223

2、配置server.xml

需要修改sequnceHandlerType 为1，1

vim conf/server.xml

0 表示是表示使用本地文件方式。
1 表示的是根据数据库来生成，就是我们自己配置的自增长
2 表示时间戳的方式 ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)
<property name="sequnceHandlerType">1</property>123456

下面是全部配置

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
        - you may not use this file except in compliance with the License. - You
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
        - - Unless required by applicable law or agreed to in writing, software -
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
        License for the specific language governing permissions and - limitations
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="serverPort">8066</property>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

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

                <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
                <property name="processorBufferPoolType">0</property>

                <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
                <property name="handleDistributedTransactions">0</property>

                        <!--
                        off heap for merge/order/group/limit      1开启   0关闭
                -->
                <property name="useOffHeapForMerge">1</property>

                <!-- 单位为m-->
                <property name="memoryPageSize">1m</property>

                <!--单位为k -->
                <property name="spillsFileBufferSize">1k</property>

                <property name="useStreamOutput">0</property>

                <!--单位为m-->
                <property name="systemReserveMemorySize">384m</property>


                <!--是否采用zookeeper协调切换  -->
                <property name="useZKSwitch">true</property>
        </system>

        <user name="root">
                <property name="password">root</property>
                <property name="schemas">yellowcong</property>
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">yellowcong</property>
                <property name="readOnly">true</property>
        </user>

</mycat:server>
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758

3、配置sequence_db_conf.properties

#配置默认的表自增长设置
vim ./conf/sequence_db_conf.properties

#添加需要自增长的表名称
TEST3=jdbc_node112345

测试插入数据

重启mycat服务后，连接数据库，测试插入数据到TEST3表

#连接mycat
mysql -h 127.0.0.1 -P 8066 -u root -proot

#使用yellowocng 数据库
use yellowcong ;

#查看表信息
show tables ;

#插入数据
#插入多条数据
insert into test3 (name) values ('doubi1'),('doubi2'),('doubi3'),('yellowcong');

#查询数据
select * from test3;123456789101112131415

查询数据，结果可以看到，角标的增长很完美。是增长的步进是1.

查看子节点，可以看到对mycat操作后，由于是全局表，所以每个子节点数据中，都存在表信息。

错误集合

ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO test2 (NULL, ‘doubi1’)

包这个问题的原因很明显，就是我们没有设置mycat中虚拟表的id自增，所以导致没有获取到id，所以导致了这个问题。

#修改后的语句
insert into test2(name) values('doubi1'),('doubi2'),('doubi3'),('yellowcong');12

ERROR 1064 (HY000): insert must provide ColumnList

必须提供添加的列，直接插入，导致了问题，所以我们需要修该插入语句

#原来语句
insert into test2 (null,'doubi1'),(null,'doubi2'),(null,'doubi3'),(null,'yellowcong');

#修改后的语句
insert into test2(name) values('doubi1'),('doubi2'),('doubi3'),('yellowcong');12345

Out of range value for column ‘id’ at row 1

导致这个问题的大致原因可能是由于mycat生成的id和数据库的字符长度对不上，超过了数据库配置的默认长度所导致的。解决办法，就是修改数server.xml，设定id生成策略是根据数据库生成的。

vim conf/server.xml

0 表示是表示使用本地文件方式。
1 表示的是根据数据库来生成
2 表示时间戳的方式 ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)
<property name="sequnceHandlerType">1</property>123456

mycat sequnce err.io.mycat.config.util.ConfigException: can’t find definition for sequence :TEST2

mycat对应sequence_db_conf.properties增加相应设置

#配置默认的表自增长设置
vim ./conf/sequence_db_conf.properties

#添加需要自增长的表名称
TEST3=jdbc_node112345

mycat sequnce err.java.lang.RuntimeException: can’t fetch sequnce in db,sequnce :TEST2

报错，说没办法获取到 id，从数据库中，这个说明我们sequence_db_conf.properties 的配置文件有问题

mycat sequnce err.java.lang.RuntimeException: can’t fetch sequnce in db,sequnce :TEST2 detail:FUNCTION mycat.mycat_seq_nextval does not exist

说没有mycat.mycat_seq_nextval 这个函数，说明在rule.xml中还需要添加这个配置

#添加表
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 100,
PRIMARY KEY (NAME)
) ENGINE = INNODB ;


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

DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) 
RETURNS varchar(64) CHARSET utf8
    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 ;

DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64)
 CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE  
                 SET current_value = current_value + increment 
                  WHERE name = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;


DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) 
RETURNS varchar(64) CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE  
                   SET current_value = value  
                   WHERE name = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;




#插入一条数据，这条数据是我们这个表所需要的。
#必须是大写的字符，不然就会报错
#插入需要自增张的表的策略
insert into  MYCAT_SEQUENCE (name,current_value,increment) values ('TEST3',0,1);