mysql sequence 自定义函数 nextval

 

创建sequence table

CREATE TABLE `sequence` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Index_2` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=637968 DEFAULT CHARSET=utf8

CREATE DEFINER=`root`@`localhost` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS bigint(20)
    DETERMINISTIC
BEGIN 
DECLARE value bigint;
SET value = 0;
SELECT 
    id
INTO value FROM
    sequence
WHERE
    name = seq_name; RETURN value;
END

CREATE DEFINER=`root`@`localhost` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS bigint(20)
    DETERMINISTIC
BEGIN DELETE FROM sequence WHERE name=seq_name; INSERT INTO sequence(name) VALUES (seq_name); RETURN currval(seq_name);
END

 

创建成功nextval&currval函数内容：

BEGIN

DELETE FROM sequence WHERE name=seq_name; INSERT INTO sequence(name) VALUES (seq_name);

RETURN currval(seq_name);
END

 

BEGIN 
 DECLARE value bigint;
 SET value = 0;
 SELECT id INTO value FROM    sequence
 WHERE  name = seq_name; RETURN value;
END

 

ibatis xml 

<selectKey resultClass="long" keyProperty="id">select nextval('base_seq') as id from dual</selectKey>