MySQL 更新 : Use index for left join, 但忽略它的 order by

Question

我使用以下查询:

update a left join b on a.type = b.type and a.name like b.pattern set a.b_id = b.id, a.flags = 1 where a.flags = 0;

b.type和b.name被定义为索引，这意味着MySQL尝试匹配这个条件:

order by b.type, b.pattern

是否可以仅将索引用于真正的匹配，而不能用于 order by 语句？

供引用:

一条以上的b记录可以匹配一条a记录，但只能使用id最小的b记录。

问题是，MySQL 不支持在连接更新中设置用户定义的 order by 语句。如果支持此功能，我可以轻松添加到末尾:

按 b.id 排序

是否可以在不使用的情况下解决问题一个选择子查询(作为连接)来实现多个表更新中的字段集顺序？

Answer 1

您不应该依赖任何应用于重复值的特定顺序(这实际上更像是“优先级”)，因为 DBMS 可以自由使用手头的任何一个，无论是否涉及到索引。

相反，您应该使用子查询来仅选择 b 中您想要使用的行。请注意，这不是 Order by，而是 Group by，因为 DBMS 在测试连接条件后仍然可以选择按其喜欢的任何顺序应用行。对于您问题中的简化情况，它看起来像这样:

Update a 
Left Join
( 
    Select type, pattern, min(id) as min_id 
    from b 
    group by type, pattern 
) as x
On a.type = x.type and a.name like x.pattern
Set a.b_id = x.min_id, a.flags = 1
Where a.flags=0;

更新:由于您已经明确需要 ID 最低的整行，因此子查询变得相当复杂 - 您必须首先找到匹配的最低 ID，然后获取该匹配的详细信息排。为了便于阅读，您可以使用临时表将其分为两个步骤，尽管具有相同内容的子查询是等效的。

-- Find "best" match in b for each row in a
Select a.id, min(b.id) as min_b_id
From a 
Left Join b

-- Apply as many join conditions as required here
On a.type = b.type and a.name like b.pattern

-- Only calculate for those rows you wish to update
Where a.flags = 0

Group by a.id

此表(或子查询)需要连接到 a (以查找要更新的行)和 b (以查找完整详细信息)更新)。