如何实现MySQL校验usersEmail与usersRefer匹配后更新usersPoint功能

原代码存在的核心问题 #

• 表名拼写错误：查询语句用了walitlist，更新语句用了waitlist，首先要统一表名
• 比较逻辑错误：if判断内误用了赋值运算符=，实际需要用相等比较运算符==
• 业务逻辑不匹配需求：原代码仅判断同一行的usersEmail和usersRefer相等，需求要求的是不同行的usersEmail与另一行的usersRefer相等
• 更新逻辑错误：仅定义了UPDATE SQL字符串，没有调用执行方法，且没有加WHERE条件，执行会直接修改全表所有数据的usersPoint
• 变量使用混乱：$pointSys同时被赋值为字段值和SQL字符串，逻辑完全不贯通

推荐实现方案：单条SQL完成需求（性能最优，无需遍历） #

直接在MySQL内执行以下语句即可完成需求，前提是你的表有唯一标识行的主键（例如下文用id作为主键，可替换为你表内的实际主键字段）：

UPDATE waitlist w1
INNER JOIN waitlist w2 
ON w1.usersEmail = w2.usersRefer AND w1.id != w2.id
SET 
  w1.usersPoint = w1.usersPoint + 100,
  w2.usersPoint = w2.usersPoint + 100;

修正后的PHP实现方案 #

如果必须用PHP逻辑实现，可参考以下代码：

// 先读取全表数据建立映射关系，避免循环查库
$emailToId = [];
$allRows = [];
$listQuery = $conn->query("SELECT id, usersEmail, usersRefer FROM waitlist");
while ($row = $listQuery->fetch_assoc()) {
    $allRows[] = $row;
    $emailToId[$row['usersEmail']] = $row['id'];
}

// 遍历校验匹配关系，执行更新
foreach ($allRows as $row) {
    $referVal = $row['usersRefer'];
    // 确认存在匹配的email，且不是同一行
    if (isset($emailToId[$referVal]) && $emailToId[$referVal] !== $row['id']) {
        // 给匹配到的email对应行加积分
        $conn->query("UPDATE waitlist SET usersPoint = usersPoint + 100 WHERE id = " . intval($emailToId[$referVal]));
        // 给当前行加积分
        $conn->query("UPDATE waitlist SET usersPoint = usersPoint + 100 WHERE id = " . intval($row['id']));
    }
}

注意：执行前请先备份表数据，避免多对一匹配导致重复加积分的问题，可新增标记字段避免重复执行逻辑。

内容的提问来源于stack exchange，提问作者Shiam Misbah