DROP TABLE IF EXISTS sys_sequence ;
CREATE TABLE sys_sequence (
seq_name VARCHAR (50) NOT NULL,
curr_value BIGINT NOT NULL DEFAULT 0,
increment_by INT NOT NULL DEFAULT 1,
PRIMARY KEY (seq_name)
) ENGINE = INNODB ;
INSERT INTO sys_sequence VALUES ('SEQ_TEST_NO',10000,1);
DELIMITER $$
DROP FUNCTION IF EXISTS currval $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION currval(v_seq_name VARCHAR (50))
RETURNS BIGINT
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE v_currval BIGINT;
SET v_currval = 1 ;
SELECT
curr_value INTO v_currval
FROM
sys_sequence
WHERE seq_name = v_seq_name ;
RETURN v_currval ;
END$$
DELIMITER ;
-- SELECT `currval`('SEQ_TEST_NO');
DELIMITER $$
DROP FUNCTION IF EXISTS `nextval` $$
CREATE FUNCTION `nextval` (`v_seq_name` VARCHAR (50)) RETURNS BIGINT (20) CONTAINS SQL
BEGIN
UPDATE
sys_sequence
SET
`curr_value` = last_insert_id(`curr_value` + `increment_by`)
WHERE `seq_name` = v_seq_name ;
RETURN last_insert_id();
END $$
DELIMITER ;
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。