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;
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
逻辑表名=节点