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文件验证一下

<?php
$sql  = "INSERT INTO `t2` VALUES(NULL ,(SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME`='t2'));";
$link = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
mysql_select_db("test");
mysql_query($sql);
mysql_close($link);
?>

用 ApacheBench 工具测试

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

查询结果（select * from t2 where id != v;）大量的行出现了 v 和 id 不相等的情况

改写下 PHP

<?php
$sql  = "INSERT INTO `t2` VALUES(NULL ,(SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME`='t2'));";

$link = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
mysql_select_db("test");
mysql_query('START TRANSACTION'); #开始事务
mysql_query($sql);
$id = mysql_insert_id();
$res = mysql_query("SELECT `v` FROM  `t2` WHERE id= ".$id);

if (!$res) {
    mysql_close($link);
    die;
}

$row = mysql_fetch_assoc($res);
if($row['v'] != $id){
    mysql_query(' ROLLBACK '); #回滚事务
}
mysql_query('COMMIT'); #提交事务
mysql_close($link);
?>

再使用 ApacheBench 测试，这次速度变慢了但是结果是都是正确的。说明整个过程需要开启事务，才能保证准确。