[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 to do this:

1. The self-increment field as the primary key. "Recommended Solution"

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

After inserting it, you can get the value of the self-growing field generated in the previous INSERT statement by performing a "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. Will not get the value after the execution of other threads in the concurrency situation.

The answer is possible, but don't be afraid, it's manageable. Only improper encoding will result in the fetching of values from 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 sense is that "SELECT @ @IDENTITY" gets the value of the previous execution of the current database connection. The values performed 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 executed simultaneously to each thread.

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           UPDATE wfo_seq                   SET current_value = current_value + increment< C13/>where name = Seq_name;           RETURN Currval (seq_name);  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

INSERT into sequence values (' Testseq ', 0, 1),----add a sequence name and initial value, and self-increment
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