Last Updated: February 25, 2016
·
1.47K
· Chuck Burgess

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.