[DB] [MySql] about getting the value of the auto-increment field and @ IDENTITY and concurrency issues
Anyone who just switched from Oracle to MySql will be confused that MySql does not have Sequence in Oracle. MySql does not have Sequence. How can I achieve the best primary key of MySql?
There are mainly the following methods:
1. the auto-increment field is used as the primary key. [Recommended solution]
Although MySql has less Sequence than Oracle, it has more self-growth characteristics of fields.
After the insert statement is complete, run SELECT @ IDENTITY to obtain the value of the auto-increment field generated in the previous insert statement.
This statement is very special. If it is not associated with a specific SQL statement, it will make people feel confused about how he gets the value. In the case of concurrency, will it obtain the value after execution by other threads.
The answer is possible, but you don't have to worry about it. It is controllable. Only improper encoding will lead to the value of other threads. Let's talk about the principle:
SUMMARYThe 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.
[SELECT @ IDENTITY] gets the value of the previous execution of the current database connection. Other connection execution values do not affect the current thread. Database connections of popular frameworks (such as Spring-jdbc, mybatis, and hibernate) all exist in ThreadLocal and are thread isolated, therefore, the [SELECT @ IDENTITY] value in other threads is not obtained. When multi-threaded programming is performed and the database connection is forcibly transmitted to each thread for simultaneous execution, the SELECT @ IDENTITY of other threads is obtained ].
2. Simulate Sequence in MySql
Step 1: Create a -- Sequence table
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;
Step 2: Create a function -- get the current value
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 ;
Step 3: Create a function -- Take the next value
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 ;
Step 4: Create a function to update the current value
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 ;
Step 5: test the Function
Select setval ('testseq ', 10); --- set the initial value of the specified sequence
Select currval ('testseq '); -- query the current value of the specified sequence
Select nextval ('testseq '); -- queries the next value of a specified sequence.