mysql 实现sequence_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会取序列值