Mysql模拟实现ORACLE的cur_val和next_val
1.创建sequence表:
CREATE TABLE `sequence` (
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`current_value` int(11) NOT NULL,
`increment` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`name`),
UNIQUE KEY `sequence_u01` (`name`) USING BTREE
)
insert into `sequence`(`name`,`current_value`,`increment`) values ('SEQ',1000,1);
2.创建currval函数
CREATE FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS INT(11)
BEGIN
DECLARE VALUE INTEGER;
SET VALUE = 0;
SELECT current_value INTO VALUE
FROM sequence
WHERE NAME = seq_name;
RETURN VALUE;
END
3.创建nextval函数
CREATE FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS INT(11)
BEGIN
UPDATE sequence SET current_value = current_value + increment
WHERE NAME = seq_name;
RETURN currval(seq_name);
END
查询:
select nextval('SYS_SEQ');
select currval('SYS_SEQ');