[DB][MySql]关于取得自增字段的值、及@@IDENTITY与并发性问题_MySQL

SUMMARY
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection. Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.

DROP TABLE IF EXISTS sequence; 
CREATE TABLE WFO_SEQ( 
 name VARCHAR(50) NOT NULL, 
 current_value INT NOT NULL, 
 increment INT NOT NULL DEFAULT 1, 
 PRIMARY KEY (name) 
) ENGINE=InnoDB; 

DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(50)) 
 RETURNS INTEGER 
 LANGUAGE SQL 
 DETERMINISTIC 
 CONTAINS SQL 
 SQL SECURITY DEFINER 
 COMMENT '' 
BEGIN 
 DECLARE value INTEGER; 
 SET value = 0; 
 SELECT current_value INTO value 
 FROM WFO_SEQ
 WHERE name = seq_name; 
 RETURN value; 
END 
$ 
DELIMITER ; 

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
 RETURNS INTEGER 
 LANGUAGE SQL 
 DETERMINISTIC 
 CONTAINS SQL 
 SQL SECURITY DEFINER 
 COMMENT '' 
BEGIN 
 DECLARE C_V INTEGER;
 
 UPDATE WFO_SEQ SET CURRENT_VALUE = CURRENT_VALUE + INCREMENT WHERE NAME = SEQ_NAME;
 
 SET C_V = CURRVAL(SEQ_NAME);
 IF C_V = -1 THEN 
 INSERT INTO WFO_SEQ(NAME, CURRENT_VALUE, INCREMENT) 
 VALUES(SEQ_NAME, 1, 1);
 RETURN 1;
 END IF;
 RETURN C_V;
END 
$ 
DELIMITER ; 

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
 RETURNS INTEGER 
 LANGUAGE SQL 
 DETERMINISTIC 
 CONTAINS SQL 
 SQL SECURITY DEFINER 
 COMMENT '' 
BEGIN 
 UPDATE WFO_SEQ
 SET current_value = value 
 WHERE name = seq_name; 
 RETURN currval(seq_name); 
END 
$ 
DELIMITER ;