MySQL inserts the field with the self-increment primary key value equal to the experiment that guarantees the data accurately under the high concurrency

Scenario Description: Table T2 has a self-increment primary key ID and field v when inserting a record requires V to be equal to the value of the ID (this is supposed to be a field that needs to be split, but the business scenario is that only some rows are equal)

One way to search on the internet is to get the ID first.

SELECT Max (ID) + 1  from T2

Then insert the obtained value to the V field

However, this is not accurate in the table with delete Row + adjusted self-increment

So change your mind and start information_schema. Reading the information from a table

INSERT  into' T2 'VALUES    (        NULL,        (            SELECT' auto_increment ' from' information_schema '. ' TABLES 'WHERE' Table_schema '= 'Test'             and' table_name '= 'T2'        )    );

The function is realized, but is it really safe?

So write a PHP file

1<?PHP2 $sql= "INSERT into ' T2 ' VALUES (NULL, [SELECT ' auto_increment ' from ' information_schema '. ' TABLES ' WHERE ' table_schema ' = ' test ') and ' table_name ' = ' T2 '); ";3 4 $link=mysql_connect("localhost", "root", "") or die("Could Not Connect:".)Mysql_error());5 mysql_select_db("Test");6 mysql_query($sql);7 Mysql_close($link);8?>

Test with the AB tool

Ab-n 50000-c http://localhost/my.php

The result: a large number of rows with V and ID unequal conditions (SELECT * from t2 where id = = v;)

Rewrite the PHP

1<?PHP2 $sql= "INSERT into ' T2 ' VALUES (NULL, [SELECT ' auto_increment ' from ' information_schema '. ' TABLES ' WHERE ' table_schema ' = ' test ') and ' table_name ' = ' T2 '); ";3 4 $link=mysql_connect("localhost", "root", "") or die("Could Not Connect:".)Mysql_error());5 mysql_select_db("Test");6 mysql_query(' START TRANSACTION ');#Start a transaction7 mysql_query($sql);8 $id=mysql_insert_id();9 $res=mysql_query("Select ' V ' from ' T2 ' WHERE id=".$id);Ten  One if(!$res) { A     Mysql_close($link); -      die; - } the  - $row=Mysql_fetch_assoc($res); - if($row[' V ']! =$id){ -     mysql_query(' ROLLBACK ');#rolling back a transaction + } - mysql_query(' COMMIT ');#Commit a transaction + Mysql_close($link); A?>

And then using the AB test, this time slows down, but the results are all right.

MySQL inserts the field with the self-increment primary key value equal to the experiment that guarantees the data accurately under the high concurrency