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.
Written by Chuck Burgess
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Mysql
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#