DELIMITER $$
CREATE DEFINER=`root`@`
localhost` FUNCTION `nextval`(`seq_name` varchar(100)) RETURNS bigint(20)
BEGIN
declare cur_val bigint(20);
SELECT sequence_cur_value
INTO cur_val
FROM gemiii.sequence_data
WHERE sequence_name = seq_name;
IF cur_val IS NOT NULL THEN
UPDATE gemiii.sequence_data
SET sequence_cur_value = IF ( (sequence_cur_value + sequence_increment) > sequence_max_value,
IF ( sequence_cycle = TRUE,
sequence_min_value,
NULL
),
sequence_cur_value + sequence_increment
)
WHERE sequence_name = seq_name;
END IF;
RETURN cur_val;
END
die Tabelle dazu sieht folgt aus :
CREATE TABLE `sequence_data` (
`sequence_name` varchar(100) NOT NULL,
`sequence_increment` int(11) unsigned NOT NULL DEFAULT '1',
`sequence_min_value` int(11) unsigned NOT NULL DEFAULT '1',
`sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT '18446744073709551615',
`sequence_cur_value` bigint(20) unsigned DEFAULT '1',
`sequence_cycle` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`sequence_name`),
UNIQUE KEY `sequence_name_UNIQUE` (`sequence_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;