场景
有两张表,gift礼品表、record记录表:
1 2 3 4 5 6 |
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='礼包码表' |
1 2 3 4 5 6 7 |
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表添加不重复的礼包码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?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表为例:
1 2 3 4 5 |
// 普通的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语句。
测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
<?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; |