mysql - 重复更新不适用于唯一索引

Question

我尝试插入/更新的 SQL 表具有以下定义:

CREATE TABLE `place`.`a_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`some_id` bigint(20) NOT NULL,
`someOther_id` bigint(20) NOT NULL,
`some_value` text,
`re_id` bigint(20) NOT NULL DEFAULT '0',
`up_id` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `some_id_key` (`some_id`),
KEY `some_id_index1` (`some_id`,`someOther_id`),
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

如您所见，some_id 和 someOther_id 共享一个索引。

我正在尝试执行如下插入/更新语句:

INSERT INTO `a_table` (`re_id`,`some_id`,`someOther_id`,`up_id`,`some_value`) VALUES      
(100,181,7,101,'stuff in the memo wow') On DUPLICATE KEY UPDATE 
`up_id`=101,`some_value`='sampleValues'

我希望因为我没有指定 id，所以它将返回到索引键 (some_id_index1) 作为插入/更新规则。但是，它只是插入。

显然这是不正确的。我在这里做错了什么？

Answer 1

OK，先帮你问出更好的问题。对您的问题“我在这里做错了什么？”的字面答案。什么都不是。

您有一个带有自动递增主键和两个非唯一二级索引的表。您在没有指定主键值的情况下向该表插入行，因此 MySQL 将使用自动递增规则为其提供唯一键，因此 ON DUPLICATE KEY条件永远不会触发。事实上，有人可能会争辩说它是多余的。

所以你要问自己的问题是你认为应该发生什么。现在 Stack Overflow 不是论坛，所以不要回来向这个问题添加评论以试图澄清您的原始问题。相反，提出一个新问题，让那些试图准确回答您所问内容的人清楚。

在我看来，有很多可能性:

1. 您想将二级索引修改为唯一的。你这样做会触发 ON DUPLICATE KEY规则并切换到更新
2. 您实际上根本不需要自动递增列和some_id实际上应该是你的主键
3. 您不了解数据库索引的工作原理(特别是您的二级索引中的至少一个可能是不必要的，因为数据库通常可以组合多个索引或使用部分索引来优化您的查询，因此第二个二级索引更多仅作为 someOther_id 字段的索引很有用 除非 您正在执行特定的唯一性约束。例如，如果您希望多行具有相同的 some_id 但只有一行一个特定的 someOther_id 对于任何特定的 some_id 则需要第二个二级索引，但是在这种情况下，第一个不需要，因为数据库可以使用第二个作为部分索引来实现相同的性能优化)

我建议您坐下来，用笔和纸远离您的计算机，并尝试以[选择一种方式]准确写下您想要做的事情:你的祖母；十一岁]可以理解。把这张纸揉成一团扔掉，直到你能一口气把它写下来而不会出错。然后返回计算机并尝试对您刚刚编写的代码进行编码。

100 次中有 99 次你实际上会发现这可以帮助你解决问题，而无需问别人问题，因为 100 次中有 99 次我们的问题是由于我们自己缺乏对问题本身的理解 。试图(虚拟地)向您的祖母或 11 岁的 child 解释您的问题会迫使您放弃一些使您蒙蔽的假设，并在您眼睛呆滞 当他们不再注意时看。 [我并不是说你真的和你的祖母/一个十一岁的 child 结对编程]

这是我为您设想的此类问题陈述的一个示例。这可能不正确，因为我不知道您的具体问题是什么:

We need a table that provides cross-reference notes about two other tables. 
There are different types of cross-reference (we use the column 're_id' to 
identify the type of cross-reference) and there are different types of notes 
(we use the columns 'up_id' as well as 'someValue') to store the actual notes.
The cross-reference is indicated with two columns 'some_id' which is the id
of the row in the 'some' table and 'someOther_id' which is the id of the row
in the 'someOther' table. There can be only one cross-reference between any one
row in the 'some' table and any one row in the 'someOther' table, but there can
be multiple cross-references from one specific row in the 'some' table to different
rows in the 'someOther' table.

根据上述问题陈述，我会将主键从自动递增切换为 (some_id,someOther_id) 上的两列主键并删除所有辅助 key !

但我希望您意识到您的实际解决方案实际上可能有所不同，因为您的问题陈述与我的猜测不同。