如何编写MySQL查询更新用户已选兴趣？求替代删除重插方案

问题背景 #

现有用户兴趣选择页面，支持用户选择3-6个兴趣，已实现首次插入功能，但更新场景采用删除用户所有旧兴趣后重新插入新选择的方案，希望得到更优的MySQL查询或实现思路。

数据库表结构：

• Interest表：id, name, description, navigation, seo_name
• member_interests表：interest_id, member_id（建议添加复合主键(interest_id, member_id)，避免重复关联）

现有方案的不足 #

删除全量旧数据再插入新数据存在以下问题：

• 产生不必要的数据库IO操作，尤其是用户仅修改少数兴趣时
• 未开启事务的情况下，中间出错会导致用户兴趣数据丢失或不全
• 操作不符合最小修改原则，不够优雅

优化思路与实现 #

思路1：仅增删差异数据 #

通过对比用户旧兴趣和新选择的兴趣，只删除不再选择的项、插入新增的项，避免全量操作。

改进后的PHP代码示例

// 获取用户当前已选的兴趣ID列表
$previousInterestIds = array_column($cms->getInterest()->getInterestName($cms->getSession()->id), 'interest_id');
$newInterestIds = array_map('intval', $interestsChosen);

// 计算需要删除的ID：旧有但不在新选择中的项
$toDelete = array_diff($previousInterestIds, $newInterestIds);
// 计算需要新增的ID：新选择但不在旧有中的项
$toAdd = array_diff($newInterestIds, $previousInterestIds);

// 开启事务（需确保DB类支持事务操作）
$this->db->beginTransaction();

try {
    // 删除用户不再选择的兴趣
    if (!empty($toDelete)) {
        $placeholders = implode(',', array_fill(0, count($toDelete), '?'));
        $sql = "DELETE FROM member_interests WHERE member_id = ? AND interest_id IN ($placeholders)";
        $params = array_merge([$cms->getSession()->id], $toDelete);
        $this->db->runSQL($sql, $params);
    }

    // 批量插入用户新增的兴趣
    if (!empty($toAdd)) {
        $valueGroups = [];
        $params = [];
        foreach ($toAdd as $interestId) {
            $valueGroups[] = '(?, ?)';
            $params[] = $interestId;
            $params[] = $cms->getSession()->id;
        }
        $sql = "INSERT INTO member_interests (interest_id, member_id) VALUES " . implode(',', $valueGroups);
        $this->db->runSQL($sql, $params);
    }

    $this->db->commit();

    // 根据用户是否有历史兴趣跳转对应页面
    if (!empty($previousInterestIds)) {
        redirect('interest-updated/', ['success' => 'Interests updated!']);
    } else {
        redirect('onboard-occupation/');
    }
} catch (Exception $e) {
    $this->db->rollback();
    $errors['message'] = 'Failed to update interests, please try again.';
}

思路2：结合MySQL的INSERT ... ON DUPLICATE KEY UPDATE（需复合主键） #

给member_interests表设置复合主键PRIMARY KEY (interest_id, member_id)后，可使用批量插入配合ON DUPLICATE KEY UPDATE避免重复插入错误，再结合思路1删除不再选择的兴趣：

-- 批量插入新兴趣，已存在则执行空操作避免报错
INSERT INTO member_interests (interest_id, member_id)
VALUES (?, ?), (?, ?), ...
ON DUPLICATE KEY UPDATE interest_id = interest_id;

额外优化建议 #

1. 添加复合主键：给member_interests表设置PRIMARY KEY (interest_id, member_id)，防止同一用户重复关联同一兴趣，同时提升查询、删除操作的效率。
2. 事务保障：所有修改操作包裹在事务中，确保数据一致性，避免中途出错导致数据异常。
3. 批量操作：插入或删除时尽量使用批量SQL语句，减少与数据库的交互次数，提升性能。

内容的提问来源于stack exchange，提问作者Ayomide Otunba