sequence mysql_mysql实现sequence | 学步园

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

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

NEXTVAL('sequence');

CURRVAL('sequence');

SETVAL('sequence',value);

-- 1创建tas_music表

DROP TABLE IF EXISTS `tas_app`.`tas_music`;

CREATE TABLE  `tas_app`.`tas_music` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` varchar(32) NOT NULL,

`icon` varchar(256) NOT NULL DEFAULT '',

`url` varchar(256) NOT NULL DEFAULT '',

`lyric` varchar(256) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2093 DEFAULT CHARSET=utf8;

-- 2创建序列表

DROP TABLE IF EXISTS `tas_app`.`tas_sequence`;

CREATE TABLE  `tas_app`.`tas_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;

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

insert into tas_app.tas_sequence values('music',23,1);

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

DELIMITER $$

DROP FUNCTION IF EXISTS `nextval` $$

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

BEGIN

UPDATE tas_sequence

SET current_value = current_value + increment

WHERE name = seq_name;

RETURN currval(seq_name);

END $$

DELIMITER ;

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

DELIMITER $$

DROP FUNCTION IF EXISTS `setval` $$

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

BEGIN

UPDATE tas_sequence

SET current_value = value

WHERE name = seq_name;

RETURN currval(seq_name);

END $$

DELIMITER ;

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

DELIMITER $$

DROP FUNCTION IF EXISTS `currval` $$

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

BEGIN

DECLARE value INTEGER;

SET value = 0;

SELECT current_value INTO value

FROM tas_sequence

WHERE name = seq_name;

RETURN value;

END $$

DELIMITER ;

-- 7创建触发器

DELIMITER $$

drop trigger if exists tas_music_trigger $$

create trigger tas_music_trigger before insert on tas_music

for each row begin

if new.id is null or new.id = '' or new.id = 0 THEN

set new.id = nextval('music');

end if;

end $$

DELIMITER ;

-- 8至此,向tas_music表插入数据时,如果id值为空,则id会取序列值.