Last Updated: February 25, 2016

PostgreSQL nextval function for MySQL

I found this MySQL nextval Function article to be very useful for adding a nextval function to MySQL. One issue that occurs with this is that the LASTINSERTID() does not get set properly. Also, there could be a small problem with table locking. I updated the code to the following:

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` boolean NOT NULL DEFAULT FALSE,
 PRIMARY KEY (`sequence_name`)
) ENGINE=MyISAM;


DELIMITER ;;
CREATE FUNCTION `nextval` (`seq_name` varchar(100)) RETURNS bigint(20) DETERMINISTIC
BEGIN
 DECLARE cur_value BIGINT(20);
 SELECT LAST_INSERT_ID(null) INTO cur_value;
 UPDATE sequence_data
 SET sequence_cur_value = LAST_INSERT_ID(
 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;
 SELECT LAST_INSERT_ID() INTO cur_value;
 RETURN cur_value;
END ;;

Note: The return was changed from NOT DETERMINISTIC to DETERMINISTIC to work for my needs.

#mysql

#nextval

#sequence table