MySQL UPDATE 与唯一键冲突

Question

表格如下：

CREATE TABLE `ToursCartsItems` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `UserId` char(40) default NULL,
  `TourId` int(10) unsigned NOT NULL,
  `CreatedAt` int(10) unsigned default NULL,
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `UniqueUserProduct` (`UserId`,`TourId`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

// simple sample data
INSERT INTO
    ToursCartsItems (UserId, TourId)
VALUES
    ("old", 1), ("old", 2), ("new", 1), ("new", 3);

所以一个用户可以拥有多个旅行（不用管旅行是什么）。 UserId 字段是 char，而不是 int，因为用户可能未登录，在这种情况下将使用会话 ID。

当用户登录时，他们的 UserID 会发生变化。所以简单的更新是

UPDATE ToursCartsItems SET UserId="new" WHERE UserId="old"
-- In reality, the new UserId would be an integer, but never mind that.

但这可能会给出重复的输入键。如果老用户和新用户都有相同的游览，我们应该在更新之前删除一个。

所以我尝试过

UPDATE ToursCartsItems
    SET UserId="in"
    WHERE UserId="out"
    AND (TourId NOT IN (SELECT TourId FROM ToursCartsItems WHERE UserId="in")
);
DELETE FROM ToursCartsItems WHERE UserId="old";

，

TRUNCATE ToursCartsItems;
INSERT INTO ToursCartsItems (UserId, TourId) VALUES ("old", 1), ("old", 2), ("new", 1), ("new", 3);
DELETE FROM ToursCartsItems WHERE UserId="old" AND TourId IN (SELECT TourId FROM ToursCartsItems WHERE UserId="new");
UPDATE ToursCartsItems SET UserId="new" WHERE UserId="old";

但都给我错误。有没有办法在 SQL 查询本身中执行此操作，或者我只需要

SELECT * FROM ToursCartsItems WHERE UserId IN ("old", "new")

在 PHP 中自己执行必要的计算？

Answer 1

我想您要做的就是首先删除冲突的记录，然后插入新记录。这可能会有所帮助...

mysql> select * from ToursCartsItems;
+----+--------+--------+-----------+
| Id | UserId | TourId | CreatedAt |
+----+--------+--------+-----------+
|  1 | old    |      1 |      NULL | 
|  2 | old    |      2 |      NULL | 
|  3 | new    |      1 |      NULL | 
|  4 | new    |      3 |      NULL | 
+----+--------+--------+-----------+
4 rows in set (0.00 sec)

mysql> delete from b using ToursCartsItems as a inner join ToursCartsItems as b on a.TourId = b.TourId where a.UserId = 'new' and b.UserId = 'old' and a.Id <> b.Id;

mysql> select * from ToursCartsItems;
+----+--------+--------+-----------+
| Id | UserId | TourId | CreatedAt |
+----+--------+--------+-----------+
|  2 | old    |      2 |      NULL | 
|  3 | new    |      1 |      NULL | 
|  4 | new    |      3 |      NULL | 
+----+--------+--------+-----------+
3 rows in set (0.00 sec)

Answer 2

我可能是错的，但这对我来说听起来像是购物车场景。为什么不将匿名和登录的游览存储在单独的表中？即，用户登录后，将匿名“购物车”旅游转换为另一个表中的实际旅游“订单”？

这样您就不会破坏任何数据。另外，你怎么知道刚刚登录的人和登录前添加游览的人是同一个人呢？