MySQL并发条件下更新数据获取唯一值的方法

会话1：

mysql> create table test (a int);
Query OK, 0 rows affected (0.18 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

会话2：

mysql> select * from test;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> update test set a=LAST_INSERT_ID(a+1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+
| a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

mysql>

会话1：

mysql> update test set a=LAST_INSERT_ID(a+1);
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+
| a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

mysql>

会话2：

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+
| a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

mysql>

由此可见，在使用LAST_INSERT_ID()函数更新字段值后，使用select LAST_INSERT_ID()，能够获取当前会话最后一次更新的数值，而不受其它会话更新的影响