MySQL SEQUENCE NUMBER

mysql-sequence.sql

--add sequence number feature to mysql

--this function generate and return sequence number of specified sequence name.
DROP FUNCTION seq;
DELIMITER //
CREATE FUNCTION seq(seq_name CHAR(20))
  RETURNS INT
  BEGIN
    UPDATE seq
    SET val = last_insert_id(val + 1), last_generate_at = NOW()
    WHERE name = seq_name;
    RETURN last_insert_id();
  END
//
DELIMITER ;


--this function generate and return sequence number of specified sequence name.
--this sequence number restart daily.
DROP FUNCTION seq_daily;
DELIMITER //
CREATE FUNCTION seq_daily(seq_name CHAR(20))
  RETURNS INT
  BEGIN
    UPDATE seq
    SET
      val              = IF(DATE(last_generate_at) = CURDATE(), last_insert_id(val + 1), last_insert_id(1)),
      last_generate_at = NOW()
    WHERE name = seq_name;
    RETURN last_insert_id();
  END
//
DELIMITER ;

CREATE TABLE `seq` (
  `name`             VARCHAR(20)      NOT NULL,
  `last_generate_at` DATETIME         NOT NULL,
  `val`              INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`name`)
)
  ENGINE = MyISAM
  DEFAULT CHARSET = latin1;

--create new sequence by name and starting number
INSERT INTO seq VALUES ('one', NOW(), 100);
INSERT INTO seq VALUES ('two', NOW(), 1000);
INSERT INTO seq VALUES ('daily_index', NOW(), 0);

--TEST: get new sequence number by name
--daily sequence start from 1 in start of each day
SELECT
  seq_daily('one'),
  seq('two'),
  seq_daily('daily_index'),
  seq_daily('daily_index');