[DB] [MySql] about getting the value of the auto-increment field and @ IDENTITY and concurrency issues

[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.