并发手动处理数据唯一（不依靠唯一索引）

1 场景
- 1.1 业务逻辑
2 分析
3 核心
- 3.1 解析sql语句
- 3.2 虚拟表 dual
4 测试

文章目录[隐藏]

场景
分析
核心
测试

场景

有两张表，gift礼品表、record记录表：

CREATE TABLE `gift` (

`cdk_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '礼包码id',

`cdk` varchar(255) NOT NULL DEFAULT '' COMMENT '礼包码',

`getAccount` varchar(255) DEFAULT NULL COMMENT '获得礼包码的用户，默认为null，不为null，则这个礼包码已被领取',

PRIMARY KEY (`cdk_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='礼包码表'

CREATE TABLE `record` (

`id` int(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '领取记录id',

`cdk` varchar(255) DEFAULT NULL COMMENT '领取的礼包码',

`sign_time` int(13) NOT NULL DEFAULT '0' COMMENT '领取时间',

`getAccount` varchar(255) DEFAULT NULL COMMENT '领取用户',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='礼包码领取记录表'

使用代码向gift表添加不重复的礼包码：

<?php

$servername = "localhost";

$username = "root";

$password = "123456";

$dbname = "test";

// 创建连接

$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接

if ($conn->connect_error) {

die("连接失败: " . $conn->connect_error);

}

// 向gift表新增30000个不重复的礼包码

for ($i=0; $i <30000 ; $i++) {

$uniqid = uniqid();

$sql = "insert into `gift`(`cdk`) values('{$uniqid}')";

$conn->query($sql);

}

检查gift表，礼包码是否有重复：

1	select `cdk` from `gift` group by `cdk` having count(`cdk`)>1;

业务逻辑

1、从gift表取出一个未被领取的礼包，更新领取人；
2、把领取的礼包码以及领取人、领取时间插入record记录表；

要求：
1、record表的礼包码必须唯一；
2、record表和gift表，同一个礼包码的领取人必须一致；

分析

1、由于gift表需要update、record表需要insert，这需要用到事务。
2、record的礼包码需要唯一，可以在cdk这个字段加一个唯一索引，这是一个方法，但是不在这次讨论范围。从上面的record表结构来看，cdk字段并没有加唯一索引，所以，需要手动处理cdk礼包码唯一的问题；还有一种场景是项目已上线，才发现record表的cdk礼包码字段没有加唯一索引，而且已产生重复的礼包码数据，在不改动已有数据的情况下，这时候再加唯一索引会报错，加不了，只能手动处理cdk礼包码字段数据唯一的问题，确保后面的数据正确。

核心

要解决以上问题，需要通过这段sql来解决：

1	insert into 表名 (字段名,...) select 前面字段对应的值,... from dual where not exists (select * from 表名 where 需要唯一的字段名=对应的值)

以record表为例：

// 普通的insert语句(不确保唯一)

insert into record (`cdk`,`getAccount`,`sign_time`) values（'626e13edd8481','jack',1651378403);

// 修改后（确保唯一）

insert into record (`cdk`,`getAccount`,`sign_time`) select '626e13edd8481','jack',1651378403 from dual where not exists (select * from record where `cdk`='626e13edd8481');

解析sql语句

第一步： select * from record where cdk='626e13edd8481'，根据需要唯一的字段，查询表里的数据；
第二步：select '626e13edd8481','jack',1651378403 from dual where not exists (...)，如果不存在数据【where not exists】，select的数据【select ‘626e13edd8481′,’jack’,1651378403】当作values插入表中；如果存在数据，则不操作（影响行数为0)。

虚拟表 dual

参考： MySQL-DUAL

不知道涉及哪个表的时候，可以指定DUAL作为一个假的表名。
DUAL单纯为了方便声明了SELECT，需要使用FROM或者其他子句的时候。MySQL可能忽略这些子句。如果没有指定表，MySQL不需要FROM DUAL。

以select '626e13edd8481','jack',1651378403 from dual where not exists (select * from record where cdk='626e13edd8481');为例，原本是想通过判断表中是否存在数据来返回select的数据作为插入数据的，即：
select '626e13edd8481','jack',1651378403 where not exists (select * from record where cdk='626e13edd8481');

但是根据sql的语法格式，是select...from 表名 where ...，还差一个表名，所以这个需要一个虚拟表dual，作为 from dual成为合法的sql语句。

测试

<?php

$servername = "localhost";

$username = "root";

$password = "123456";

$dbname = "test";

// 创建连接

$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接

if ($conn->connect_error) {

die("连接失败: " . $conn->connect_error);

}

echo '<pre>';

// 操作3000次

for ($i=0; $i <3000 ; $i++) {

shiwu($conn);

}

function shiwu($conn) {

$user = GetRandStr(3); // 随机用户名

$nowTime = time();

// 开启事务

$conn->autocommit(false);

// 从礼包表中获取未被领用的礼包码

$sql = "select * from gift where getAccount is null limit 1";

$result = $conn->query($sql)->fetch_assoc();

// 更新礼包表

$giftSql = "update gift set `getAccount`='{$user}' where `cdk_id`='{$result['cdk_id']}'";

$giftResult = $conn->query($giftSql);

// 新增礼包领取记录

// $recordSql = "insert into record (`cdk`,`getAccount`,`sign_time`) values ('{$result['cdk']}','{$user}',{$nowTime})"; // 普通的insert语句，用作测试对比

$recordSql = "insert record (`cdk`,`getAccount`,`sign_time`) select '{$result['cdk']}','{$user}',{$nowTime} from dual where not exists (select * from record where `cdk`='{$result['cdk']}')";

$recordResult = $conn->query($recordSql);

// 获取礼包领取记录插入影响行数

// 说明：如果表已存在唯一的数据，执行后，会返回true,影响行数0；不存在唯一的数据，执行后，会返回true,影响行数1

// 所以这里需要根据影响行数去做事务的判断

$recordAffectRows = $conn->affected_rows;

$debugData = [

'cdk_id'=>$result['cdk_id'],

'cdk'=>$result['cdk'],

'getAccount'=>$user,

'sign_time'=>$nowTime

];

if ($recordAffectRows>0 && $giftResult && $recordResult) {

$conn->commit(); //提交事务

echo '成功: '.json_encode($debugData).PHP_EOL;

} else {

$conn->rollback();

echo '<h5>失败: '.json_encode($debugData).'</h5>'.PHP_EOL;

}

$conn->autocommit(TRUE); //开启自动提交功能

}

function GetRandStr($length){

//字符组合

$str = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

$len = strlen($str)-1;

$randstr = '';

for ($i=0;$i<$length;$i++) {

$num=mt_rand(0,$len);

$randstr .= $str[$num];

}

return $randstr;

}

$conn->close();

开两个窗口模拟两个用户在操作

检查record表的礼包码有没有重复

1	select `cdk` from `record` group by `cdk` having count(`cdk`)>1;

文章并发手动处理数据唯一（不依靠唯一索引）转载需要注明出处

场景

业务逻辑

分析

核心

解析sql语句

虚拟表 dual

测试

您必须 登录 才能发表评论！

您必须登录才能发表评论！