主页 / dba / 问题 / 297565
Accepted
Sahasrar
Asked: 2021-08-04 22:48:58 +0800 CST

MySQL：在两行之间交换 ID

  • 772

我们有现有的表：

| id |    name    |   color   | calories |
------------------------------------------
| 1  | apple      | red       | 20       |
| 2  | orange     | orange    | 10       |
| 3  | grapes     | green     | 5        |
| 4  | bananas    | yellow    | 15       |
| 5  | plum       | purple    | 25       |
------------------------------------------

列id 是唯一的和主键。我在编辑/更新记录时遇到问题，例如，我注意更改第 3 行 where、id = 3toid = 5和第 5 行 where、id = 5to id = 3... 就像第 3 行和第 5 行之间交换 id。当然有重复，但是如何解决这种情况...

但是 MySQL 在逻辑上抛出了错误。

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5' for key 'PRIMARY'

我知道这是愚蠢的情况，但我们有一个客户想要那样做。他想编辑/更新每个现有记录。

mysql update

5 个回答

  1. 列 id 是唯一的和主键......就像第 3 行和第 5 行之间的交换 id。

    你不应该这样做。

    任何记录的主键都应该在第一次创建记录时创建/设置/生成，并且它不应该改变，而不是在该记录的整个生命周期内，直到记录最终被删除。

    想象一下，如果银行对人们的银行账户重新编号，那将是多么混乱！

    此外，虽然您似乎还没有在这里拥有它们，但拥有主键的主要好处之一是其他表可以拥有引用它的外键。如果您开始更改主键值，那么您还必须更改所有相应的外键值（数据库将强制执行此操作）。
    我严重怀疑任何用户都会理解这一点。

    ...客户...想要编辑/更新每个现有记录。

    无论如何，允许他们编辑数据，而不是主键。
    我将假设您为此提供了某种 UI，并且他们没有直接针对数据库发出 SQL。

    如果他们试图将其用作某种序列或“顺序”，那么使用单独的列是可行的方法。主键，甚至是自增主键，都保证是唯一的；它们不能保证是连续的或连续的。

    • 17
  2. Best Answer

    这些应该使用中间值包装在事务中；

    begin;
set @from = 3;
set @to = 5;
set @tmpid = (2000000 + @from % 147483647);
update col set id=@tmpid where id = @from;
update col set id=@from where id=@to;
update col set id=@to where id = @tmpid;
commit;

    选择高端 @tmpid 命名空间以避免冲突很重要，以免以后遇到麻烦。

    • 7

  3. 我同意更改 id 总是一个坏主意，尤其是当它们用于外键时。

    我对三角形交换也不是很满意。

    因此，当您尝试更新旧 id 并且非常确定已经进行了备份并考虑了所有可能暗示的后果时。

    您可以使用存储过程，这将是节省（从 Charlieface 借来的示例）

    create table t(id int UNIQUE,v varchar(10));

    insert into t (id,v)
values (2,'c'),(3,'a'), (5,'b');

    select * from t

    编号 | v
-: | :-
 2 | C
 3 | 一个
 5 | b

    CREATE PROCEDURE procedure_name(IN _a BIGint , IN _b BIGint)
BEGIN
   CREATE TEMPORARY TABLE T1 SELECT _b,v  FROM t WHERE id = _a;
   DELETE FROM t WHERE id = _a;
   UPDATE t SET id = _a WHERE id = _b;
   INSERT INTO t SELECT * FROM T1;
END 

    CALL procedure_name(3,5)

    select * from t

    编号 | v
-: | :-
 2 | C
 3 | b
 5 | 一个

    db<>在这里摆弄

    • 2

  4. 您可以将更新作为单个语句进行，但这仅适用于 SQL Server，不适用于 MySQL

    update col
set id = case when id = 3 then 5 else 3 end
where id in (3, 5);

    SQL Server 数据库<>小提琴

    MySQL 数据库<>小提琴

    在 SQL Server 中，update主键（或任何唯一键）在语句级计算。它通过使用Split->Sort->Collapse计划在内部执行此操作，这意味着它实际上变成了删除、更新和插入，因此没有违反约束。

    另见Paul White和Fabiano Amorim的这两篇文章

    约束的正确实现将具有IMMEDIATEorDEFERRED选项。这两者都需要在语句级别而不是行级别进行约束检查。

    MySQL 都不支持，因为它逐行检查约束。PostgreSQL 还检查IMMEDIATE行级别的约束

    但是 SQL Server 只检查语句级别。您可以在文档中看到这一点。

    • 1

  5. 正如其他人所说，交换 ID 没有任何意义。

    我认为这个特定客户的最佳方法是让他们进行编辑（例如在电子表格中），然后截断表格并重新插入客户指定的行。

    • -1

相关问题