[DB] [MYSQL] about getting the value of the self-increment field, and the @ @IDENTITY and concurrency issues

For those who have just turned to MySQL from Oracle, there is no confusion in MySQL for sequence in Oracle. MySQL does not have sequence, then MySQL's primary key in what way to achieve the best?

There are several main ways:

1. The self-increment field as the primary key.

"Recommended Solution"

Although MySQL is less sequence than Oracle, it is more of a self-growing feature of the field.

After inserting, you can get the value of the self-growing field generated in the previous INSERT statement by running "SELECT @ @IDENTITY".

This statement is very special, not related to a specific SQL statement, it will make people feel confused, how he gets the value. In the case of concurrency, it will not get the value after the other thread runs.

The answer is possible, but it is not to be feared or controllable. Only improper coding can result in the value being taken to other threads. First, the principle:

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

The general meaning is "SELECT @ @IDENTITY" gets the value of the previous run of the current database connection. The values that are run by other connections do not affect the current thread. The current popular frameworks (such as SPRING-JDBC, MyBatis, Hibernate) database connections are present in threadlocal and are thread-isolated, so the "SELECT @ @IDENTITY" value in other threads is not obtained.

The "SELECT @ @IDENTITY" of other threads is only taken when multithreaded programming, forcing the database connection to be passed to each thread at the same time.

2. Simulate sequence in MySQL

First step: Create a--sequence management table

DROP TABLE IF EXISTS sequence;  CREATE TABLE wfo_seq (           name VARCHAR () not NULL,           Current_value int. NOT NULL,           increment int not null, DEFAULT 1,           PRIMARY KEY (name)  ) Engine=innodb;  

Step Two: Create-a function that takes the current value

DROP FUNCTION IF EXISTS currval;  DELIMITER $  CREATE FUNCTION currval (seq_name VARCHAR)           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 Three: Create--a function to remove a value

DROP FUNCTION IF EXISTS nextval;    DELIMITER $    CREATE FUNCTION nextval (seq_name VARCHAR)             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;  

Fourth Step: Create-a function to update the current value

DROP FUNCTION IF EXISTS setval;  DELIMITER $  CREATE FUNCTION setval (Seq_name VARCHAR (), 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;  

Fifth Step: Test function function

SELECT setval (' Testseq ', ten);---Set the initial value of the specified sequence
SELECT currval (' testseq ');--Query the current value of the specified sequence
SELECT nextval (' testseq ');--Query the next value of the specified sequence

[DB] [MYSQL] about getting the value of the self-increment field, and the @ @IDENTITY and concurrency issues