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 ;