MYSQL手动创建序列
**
第一步创建一个序列表
CREATE TABLE IF NOT EXISTS sequence
(
name
varchar(50) NOT NULL,
current_value
int(11) NOT NULL,
increment
int(11) NOT NULL DEFAULT ‘1’ ,
PRIMARY KEY (name
)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT=‘序列表,命名s_[table_name]’;
插入一条序列,这表每条数据都是一个序列分别为(序列名,序列当前值,序列步长)
INSERT INTO sequence
(name
, current_value
, increment
) VALUES
(‘S_RULEAUTO’, 0, 1);
创建查询函数,返回序列当前值。
DROP FUNCTION IF EXISTS currval
;
DELIMITER //
CREATE FUNCTION currval
(seq_name VARCHAR(50)) RETURNS int(11)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE VALUE INTEGER;
SET VALUE = 0;
SELECT current_value INTO VALUE FROM sequence WHERE NAME = seq_name;
RETURN VALUE;
END//
DELIMITER ;
查询序列下一个值
DROP FUNCTION IF EXISTS nextval
;
DELIMITER //
CREATE FUNCTION nextval
(seq_name VARCHAR(50)) RETURNS int(11)
DETERMINISTIC
BEGIN
UPDATE sequence SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN currval(seq_name);
END//
DELIMITER ;
测试例子
select nextval(“S_RULEAUTO”)
SELECT CURRVAL(“S_RULEAUTO”);