更新 MySQL 中的唯一键或主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16448120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update unique or primary keys in MySQL
提问by siberius.k
I'm building a database management tool for a client to use on his own, and I'm having some problem dealing with the possibility of the update of primary/unique keys. So, given that the data for the update is passed by a PHP script on a per row basis, here's what I've come up with (from "immediatly" to "after some time"):
我正在构建一个供客户自己使用的数据库管理工具，并且在处理主键/唯一键更新的可能性时遇到了一些问题。因此，鉴于更新的数据是由 PHP 脚本逐行传递的，这就是我想出的（从“立即”到“一段时间后”）：
DELETE/INSERT instead of UPDATE (awful, I now...):
DELETE FROM table WHERE unique_key=x; DELETE FROM table WHERE unique_key=y; INSERT INTO table VALUES (unique_key=y, field=record1), (unique_key=x, field=record2);Alter my primary/unique key and then substitute them with the modified value:
UPDATE table SET unique_key=x* WHERE unique_key=x; UPDATE table SET unique_key=y* WHERE unique_key=y; UPDATE table SET unique_key=y WHERE unique_key=x*; UPDATE table SET unique_key=x WHERE unique_key=y*;- Add a not modifiable auto_increment field "id" to all my tables, which act as a surrogate primary key
删除/插入而不是更新（糟糕，我现在......）：
DELETE FROM table WHERE unique_key=x; DELETE FROM table WHERE unique_key=y; INSERT INTO table VALUES (unique_key=y, field=record1), (unique_key=x, field=record2);更改我的主键/唯一键，然后用修改后的值替换它们：
UPDATE table SET unique_key=x* WHERE unique_key=x; UPDATE table SET unique_key=y* WHERE unique_key=y; UPDATE table SET unique_key=y WHERE unique_key=x*; UPDATE table SET unique_key=x WHERE unique_key=y*;- 向我的所有表添加一个不可修改的 auto_increment 字段“id”，作为代理主键
As now, I'm on the route of adding an "id" field to everything. Other options?
现在，我正在为所有内容添加“id”字段。其他选择？
采纳答案by Mike Sherrill 'Cat Recall'
Updating a primary key isn't a problem; all values in SQL (and in the relational model) are supposed to be updatable.
更新主键不是问题；SQL（和关系模型）中的所有值都应该是可更新的。
The problem seems to be swappingprimary keys, which
问题似乎是交换主键，这
- doesn't make sense to me if you use surrogate keys (because they're meaningless, so updates aren't necessary) and which
- doesn't make sense to me if you use natural keys, because that's like swapping myStackOverflow userid with yours.
- 如果您使用代理键（因为它们毫无意义，因此不需要更新），那么对我来说没有意义
- 如果您使用自然键，对我来说没有意义，因为这就像将我的StackOverflow 用户 ID交换为您的用户 ID。
Adding an "ID" column to every table won't help you. The "unique_key" column stillhas to be declared unique. Adding an "ID" column doesn't change that business requirement.
向每个表添加“ID”列对您没有帮助。“unique_key”列仍然必须声明为唯一的。添加“ID”列不会改变该业务需求。
You couldswap primary key values if MySQL supported deferred constraints. (Deferred constraints are a feature in standard SQL.) But MySQL doesn't support that feature. In PostgreSQL, for example, you could do this.
如果 MySQL 支持延迟约束，您可以交换主键值。（延迟约束是标准 SQL 中的一个特性。）但 MySQL 不支持该特性。例如，在 PostgreSQL 中，您可以这样做。
create table test (
unique_key char(1) primary key deferrable initially immediate,
other_column varchar(15) not null
);
insert into test values
('x', 'record2'),
('y', 'record1');
begin;
set constraints test_pkey deferred;
update test set unique_key = 'y' where other_column = 'record2';
update test set unique_key = 'x' where other_column = 'record1';
commit;
select * from test;
unique_key other_column
--
y record2
x record1
回答by nvogel
You should be able to use a CASE expression to do this kind of update. For example:
您应该能够使用 CASE 表达式来进行这种更新。例如：
UPDATE tbl SET col =
CASE WHEN col = 1 THEN 2
WHEN col = 2 THEN 1
END
WHERE col IN (1,2);
(untested code)
（未经测试的代码）
