MYSQL实现ORACLE的nextval方式
新建函数,如下:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `nextval`(seq_name varchar(100)) RETURNS bigint(20)
BEGIN
DECLARE cur_val bigint(20);
SELECT cur_value INTO cur_val FROM sequence WHERE name = seq_name;
IF cur_val IS NOT NULL THEN
UPDATE
sequence
SET cur_value = cur_value + increment
WHERE name = seq_name;
END IF;
RETURN cur_val;
END
新建表sequence
CREATE TABLE `sequence` (
`name` varchar(100) NOT NULL,
`increment` int(11) unsigned NOT NULL DEFAULT '1',
`cur_value` bigint(20) unsigned DEFAULT '1',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
应用使用SSI,通过 select nextval('SEQ') 获得序列值。
遇到的问题:当select nextval('A') 后,再在另外的功能点中使用select nextval('B') 时,提示无column nextval('A'),抛出SQL异常。开始没搞明白,百度&谷歌都出动了。后来细想了下,应该是第2次的时候nextval('A')被当成了别名。故在ibatis的SQL配置中稍作修改select nextval('A') as x,就OK了。