mysql 函数模拟序列

mysql本身不提供序列机制，但是可以通过函数来模拟实现序列

CREATE TABLE IF NOT EXISTS `sequence`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `current_value` int(11) NULL DEFAULT NULL,
  `increment` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;

INSERT INTO `sequence` (`id`, `current_value`, `increment`) VALUES ('demo', 0, 1);

DROP FUNCTION IF EXISTS `current_sequence`;  
DELIMITER //  
CREATE  FUNCTION `current_sequence`(seq_id VARCHAR(255)) RETURNS int(11)  
    READS SQL DATA  
    DETERMINISTIC  
BEGIN  
    DECLARE VALUE INTEGER;  
    SET VALUE = 0;  
    SELECT current_value INTO VALUE FROM sequence WHERE id = seq_id;  
    RETURN VALUE;  
END//  
DELIMITER ;


DROP FUNCTION IF EXISTS `next_sequence`;  
DELIMITER //  
CREATE  FUNCTION `next_sequence`(seq_id VARCHAR(255)) RETURNS int(11)  
    DETERMINISTIC  
BEGIN  
UPDATE sequence SET current_value = current_value + increment WHERE id = seq_id;  
RETURN current_sequence(seq_id);  
END//  
DELIMITER ;

 

转载于:https://www.cnblogs.com/relucent/p/10215729.html