mysql实现sequence_mysql实现Oracle中sequence函数的方法

如何在mysql数据里使用Oracle序列语法.NEXTVAL和.CURVAL.

假设在mysql中序列的语法是:

NEXTVAL('sequence');

CURRVAL('sequence');

SETVAL('sequence',value);

--1创建序列表

CREATE TABLE`lndl`.`LNDL_SEQUENCE` (

`NAME` varchar(50) NOT NULL,

`CURRENT_VALUE` int(11) NOT NULL,

`INCREMENT` int(11) NOT NULL DEFAULT '1',

PRIMARY KEY (`NAME`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--2序列表里插入如下记录

insert into test.LNDL_SEQUENCE values('CONF_DEV',10000,1);

--3创建nextval function的代码如下:

DELIMITER $$

DROP FUNCTION IF EXISTS `nextval` $$

CREATE DEFINER=`lndl`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)

BEGIN

UPDATE LNDL_SEQUENCE

SET CURRENT_VALUE = CURRENT_VALUE + INCREMENT

WHERE NAME = seq_name;

RETURN currval(seq_name);

END $$

DELIMITER ;

-- 4创建setval function的代码如下:

DELIMITER $$

DROP FUNCTION IF EXISTS `setval` $$

CREATE DEFINER=`lndl`@`%` FUNCTION `setval`(seq_name VARCHAR(50), value INTEGER) RETURNS int(11)

BEGIN

UPDATE LNDL_SEQUENCE

SET CURRENT_VALUE = value

WHERE NAME = seq_name;

RETURN currval(seq_name);

END $$

DELIMITER ;

-- 5创建currval function的代码如下:

DELIMITER $$

DROP FUNCTION IF EXISTS `currval` $$

CREATE DEFINER=`lndl`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)

BEGIN

DECLARE value INTEGER;

SET value = 0;

SELECT CURRENT_VALUE INTO value

FROM LNDL_SEQUENCE

WHERE NAME = seq_name;

RETURN value;

END $$

DELIMITER ;

--6测试

mysql> select * from lndl_sequence;

+----------+---------------+-----------+

| name| current_value | increment |

+----------+---------------+-----------+

| conf_dev |10010 |1 |

+----------+---------------+-----------+

1 row in set

mysql> select nextval('conf_dev');

+---------------------+

| nextval('conf_dev') |

+---------------------+

|10011 |

+---------------------+

1 row in set