mysql - 重复 key 更新半工作

Question

我有一个购物车作为名为 cart 的数据库表，用于存储每个 SaaS 实例购物车中的商品记录。我想要做的是，如果将产品添加到用户的购物车并且已经存在相同的记录，则仅当状态 = 时，才将表中的数量列更新为等于旧值加上新值0，否则，输入新记录。

分割:

如果该商品已在购物车中，则仅当状态列 = 0 时才更新数量列以设置新数量值，否则，将该商品作为新行添加到购物车表中。

这是我到目前为止所得到的:

    $itemList = $db->query("INSERT INTO 

                                        cart(
                                        `lmsid`, 
                                        `userid`, 
                                        `courseid`, 
                                        `assigned_by`, 
                                        `assigned_on`, 
                                        `quantity`) 

                            VALUES(

                                        :lmsid, 
                                        :userid, 
                                        :courseid, 
                                        :assigned_by, 
                                        :assigned_on, 
                                        1) 

                            ON DUPLICATE KEY UPDATE 

                                        `quantity` = `quantity` + values('quantity')
                            )", 

                            array(
                            "lmsid"=>$core['id'], 
                            "userid"=>$user,
                            "courseid"=>$_POST['courseid'], 
                            "assigned_by"=>$userInfo['id'], 
                            "assigned_on"=>time())
    );

目前...

1. 如果一条记录根本不存在，它会被完美插入。
2. 如果存在状态 = 0 的记录，则会完美更新现有数量。
3. 如果存在状态 = 2 的记录，则查询失败并显示:

未处理的异常。 SQLSTATE[23000]:违反完整性约束:1062 键“useridAndCourseid”的重复条目“66-2”

表中的关键useridAndCourseid是:

UNIQUE KEY `useridAndCourseid` (`userid`,`courseid`)

当产品从购物车中删除时，状态列会更新为“1”，该记录将永远保留在那里以供我们记录。当产品已付款时，状态会更新为“2”，再次记录在案。当一条记录只是放在购物车中等待处理时，它的状态为 0。

我需要什么帮助

目前查询已完成一半工作，但我只是不知道怎么说“如果记录存在且状态 = 0 更新数量，否则插入新记录”。这实际上是我想要实现的目标，但我想我试图让它变得过于复杂，或者我花了很长时间试图让它变得过于复杂，我忘记了我在追求什么!我确信有一个简单的解决方案。如果我可以使用 ON DUPLICATE KEY 和 WHERE 子句就好了...

感谢您的帮助!

Answer 1

有多种方法可以解决这个问题。但是，如果您想要使用单个语句进行插入，则需要创建一个唯一索引来执行您想要的操作。该索引应该有一列在 status = 0 时采用某个值(例如 0)，在其他时间采用 NULL。 NULL 值不会被视为重复键。您可以将其称为 IsActiveFlag。

如果你有这样的列，那么索引:

create index idx_cart(userid, courseid, IsActiveFlag)

应该允许你做你想做的事。