mycat分表自增 db实现

CREATE TABLE TEST_SEQUENCE (name VARCHAR(50) NOT NULL,current_value INT NOT

NULL,increment INT NOT NULL DEFAULT 1, PRIMARY KEY(name)) ENGINE=InnoDB;

increment为步长

name为逻辑表名

插入数据

INSERT INTO `TEST_SEQUENCE` (`name`, `current_value`, `increment`)
VALUES
('NIGGERS', 1002, 1);

对应逻辑表NIGGERS

mysql functions：

/* 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 TEST_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name; RETURN mycat_seq_currval(seq_name);
END ;;
DELIMITER ;; */

/* DELIMITER ;;
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64)
CHARSET utf8 DETERMINISTIC
BEGIN
UPDATE TEST_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name); END ;;
DELIMITER ;; */

/* 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
TEST_SEQUENCE WHERE name = seq_name; RETURN retval;
END ;;
DELIMITER ;; */

配置：

server.xml

<property name="sequnceHandlerType">1</property> 2是时间戳方式

scheme.xml

<table name="niggers" autoIncrement="true" primaryKey="id" subTables="niggers$1-3" dataNode="test"rule="mod-long"></table>
<table name="test_sequence" primaryKey="name" dataNode="test"></table>

sequence_db_conf.xml

NIGGERS=test

逻辑表名=节点