你的位置:首页 > 数据库

[数据库]MySQL 插入与自增主键值相等的字段 与 高并发下保证数据准确的实验


场景描述: 表t2 中 有 自增主键 id  和 字段v  当插入记录的时候 要求 v与id 的值相等(按理来说这样的字段是需要拆表的,但是业务场景是 只有某些行相等 )

在网上搜的一种办法是 先获取自增ID 

SELECT max(id)+1 from t2

然后给v字段插入获取到的值

但是这样的做法在有删除行+调整过自增值的表中是不准确的

于是换个思路 从 information_schema 下手 读取表的信息

INSERT INTO `t2`VALUES  (    NULL,    (      SELECT        `AUTO_INCREMENT`      FROM        `information_schema`.`TABLES`      WHERE        `TABLE_SCHEMA` = 'test'      AND `TABLE_NAME` = 't2'    )  );

功能是实现了 但是真的安全么

于是写个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($sql);7 mysql_close($link);8 ?>

用ab工具测试

ab -n 50000 -c 20 http://localhost/my.php

结果是:大量的行出现了 v 和 id 不相等的情况(select * from t2 where  id != v;)

改写下 PHP

 1 <?php 2 $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'); #开始事务 7 mysql_query($sql); 8 $id = mysql_insert_id(); 9 $res = mysql_query("SELECT `v` FROM `t2` WHERE id= ".$id);10 11 if (!$res) {12   mysql_close($link);13   die;14 }15 16 $row = mysql_fetch_assoc($res);17 if($row['v'] != $id){18   mysql_query(' ROLLBACK '); #回滚事务19 }20 mysql_query('COMMIT'); #提交事务21 mysql_close($link);22 ?>

再使用AB测试,这次速度变慢了 但是结果是都是正确的