MySQL 降级复合主键以更具限制性

Question

我有下表用于跟踪正在观看支持票的用户

CREATE TABLE IF NOT EXISTS crm_ticketwatcher (
  ticketid int(10) unsigned NOT NULL DEFAULT '0',
  employeeid int(10) unsigned NOT NULL DEFAULT '0',
  contactid int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (ticketid,employeeid,contactid)
) ENGINE=MyISAM;

我想要一个从主键中删除 contactid 的降级脚本。

表格内容看起来有点像这样

ticketid|employeeid|contactid
--------|----------|---------
5       |5         |0
5       |8         |0
5       |0         |2
5       |0         |3

当我运行我的(不成功的)降级脚本时，

ALTER TABLE crm_ticketwatcher DROP PRIMARY KEY, ADD PRIMARY KEY (ticketid, employeeid);

我收到以下错误:

第 1 行的错误 1062 (23000):键“PRIMARY”的重复条目“306-0”

因为现在有 2 行设置了主键 (5, 0)

从表中删除额外行并保存最后出现的行的最佳方法是什么？

我们使用的是 MySQL 5.7.13，因此 IGNORE 关键字不是一个选项。

谢谢。

Answer 1

根据 ALTER TABLE Syntax 记录:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

因此，您可以:

ALTER IGNORE TABLE crm_ticketwatcher
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (ticketid, employeeid);

但是请注意，保留哪些“重复”行将是不确定的。

如果您想要更确定的结果，或者正在使用 MySQL v5.7.4 及更高版本(IGNORE 已从中删除)，您可以首先使用多表 DELETE 语法:

DELETE c1
FROM   crm_ticketwatcher c1
  JOIN crm_ticketwatcher c2 USING (ticketid, employeeid)
WHERE  c1.contactid < c2.contactid -- or whatever logic you prefer