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);