MySQL 5.7.39中ON DUPLICATE KEY UPDATE实现心愿单无重复添加问题

现有表结构 #

create table wishlist
(
    userID       int                          null,
    wishlistData longtext collate utf8mb4_bin not null,
    constraint userID
        unique (userID),
    constraint whishlist_user_id_fk
        foreign key (userID) references user (id)
);

最初的问题SQL #

以下语句会导致wishlistData中出现重复商品ID：

$query = "INSERT INTO wishlist (userID, wishlistData)
                VALUES ($userID, JSON_ARRAY('$productId'))
                ON DUPLICATE KEY UPDATE wishlistData = JSON_ARRAY_INSERT(wishlistData, '$[0]', '$productId');";

需求 #

• 若userID不存在，插入新记录；
• 若userID已存在，向wishlistData中添加商品ID，但该ID已存在时不执行更新。

尝试的无效SQL #

$query = "INSERT INTO wishlist (userID, wishlistData)
                VALUES ($userID, JSON_ARRAY('$productId'))
                ON DUPLICATE KEY UPDATE wishlistData = JSON_ARRAY_INSERT(wishlistData, '$[0]', IF ((SELECT JSON_SEARCH(JSON_EXTRACT(wishlistData, '$'), 'one', '$productId')  IS Not NULL),wishlistData,'$productId'));";

解决方案 #

修正后的SQL逻辑：先判断商品ID是否已存在于JSON数组中，不存在才执行追加操作：

$query = "INSERT INTO wishlist (userID, wishlistData)
          VALUES ($userID, JSON_ARRAY('$productId'))
          ON DUPLICATE KEY UPDATE wishlistData = IF(
              JSON_CONTAINS(wishlistData, JSON_QUOTE('$productId')),
              wishlistData,
              JSON_ARRAY_APPEND(wishlistData, '$', '$productId')
          );";

关键点说明

1. 重复判断：使用JSON_CONTAINS检查数组中是否存在目标ID，JSON_QUOTE用于将商品ID转换为符合JSON格式的字符串，确保匹配准确。
2. 更新逻辑：如果ID已存在，直接返回原wishlistData不修改；不存在则用JSON_ARRAY_APPEND将ID追加到数组末尾（若要插入到数组开头，可替换为JSON_ARRAY_INSERT(wishlistData, '$[0]', '$productId')）。
3. 安全提示：直接拼接变量存在SQL注入风险，建议使用PDO或mysqli的预处理语句绑定参数，例如：

// PDO预处理示例
$stmt = $pdo->prepare("INSERT INTO wishlist (userID, wishlistData)
                       VALUES (?, JSON_ARRAY(?))
                       ON DUPLICATE KEY UPDATE wishlistData = IF(
                           JSON_CONTAINS(wishlistData, JSON_QUOTE(?)),
                           wishlistData,
                           JSON_ARRAY_APPEND(wishlistData, '$', ?)
                       );");
$stmt->execute([$userID, $productId, $productId, $productId]);

内容的提问来源于stack exchange，提问作者Rami Nasfi