Mysql´æ´¢¹ý³Ì£¨Î壩——SEQUENCEµÄʵÏÖ

ÔÚ
oracle
ÖУ¬
sequence
Ìṩ¶à±í¶à×ֶοɹ²ÓÃÒ»¸ö²»Öظ´Öµ¡£
Mysql
ÖдæÔÚ×ÔÔöÁУ¬»ù±¾¿ÉÒÔÂú×ã
PK
µÄÒªÇó¡£µ«×ÔÔöÁдæÔÚÏÞÖÆ£º
a.
Ö»ÄÜÓÃÓÚ±íÖеÄÒ»¸ö×ֶΣ¬Ò»ÕŲ»ÄÜͬʱ´æÔÚÁ½¸öÒÔÉϵÄ×ÔÔöÁÐ
;
b.
×ÔÔöÁбØÐë±»¶¨ÒåΪ
key
£¨
PK
»ò
FK
£©
;
c.
×ÔÔöÁв»Äܱ»¶à¸ö±í¹²ÓÃ
;
d.
µ±
insert
Óï¾ä²»°üÀ¨×ÔÔö×ֶλò½«ÆäÖµÉèÖÃΪ
NULL
ʱ£¬¸ÃÖµ»á×Ô¶¯ÌîÉÏ¡£
                  
ÔÚ²»ÒªÇó×Ö¶Î˳ÐòµÝÔöµÄÇé¿öÏ£¬¿ÉÒÔÔÚ
Mysql
ÖÐʵÏÖÐòÁУº
        
Sql
code 5-1:
                  
DROP TABLE IF EXISTS sequence;

-- ½¨sequence±í£¬Ö¸¶¨seqÁÐΪÎÞ·ûºÅ´óÕûÐÍ£¬¿ÉÖ§³ÖÎÞ·ûºÅÖµ£º0(default)µ½18446744073709551615£¨0µ½2^64–1£©¡£
CREATE TABLE sequence (
name VARCHAR(50) NOT NULL,
current_value BIGINT UNSIGNED NOT NULL DEFAULT 0,
increment INT NOT NULL DEFAULT 1,
PRIMARY KEY (name) -- ²»ÔÊÐíÖظ´seqµÄ´æÔÚ¡£
) ENGINE=InnoDB;


DELIMITER /

DROP FUNCTION IF EXISTS currval /

CREATE FUNCTION currval(seq_name VARCHAR(50))
RETURNS BIGINT
BEGIN
DECLARE value BIGINT;
SELECT current_value INTO value
from sequence
WHERE upper(name) = upper(seq_name); -- ´óСд²»Çø·Ö.
RETURN value;
END;
/

DELIMITER ;


DELIMITER /

DROP FUNCTION IF EXISTS nextval /

CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS BIGINT
BEGIN
DECLARE value BIGINT;
UPDATE sequence
SET current_value = current_value + increment
WHERE upper(name) = upper(seq_name);
RETURN currval(seq_name);
END;
/

DELIMITER ;

DELIMITER /

DROP FUNCTION IF EXISTS setval /

CREATE FUNCTION setval (seq_name VARCHAR(50), value BIGINT)
RETURNS BIGINT
BEGIN
UPDATE seque