MYSQL WHERE-IN子查询永远运行 [英] MYSQL WHERE-IN Subquery Runs Forever

问题描述

我有一个MySQL表.我们称它为小部件.窗口小部件表具有3个字段: id ， type_id 和 name .我想在一个查询中获取与名为'doodad'的小部件共享 type_id 的所有小部件.我已经写了2个查询:

I have a MySQL table. Let's call it Widgets. The Widget table has 3 fields: id, type_id, and name. I want, in one query, to get all the widgets that share a type_id with the Widget named 'doodad'. I've written 2 queries:

1. 请给我名为"doodad"的小部件的type_id.
2. 给我所有具有该type_id的小部件.

这有效.每个查询都独立实现其目标.

This works. Each query, independently achieves its goal.

但是，当我将它们组合成一个嵌套查询时，它将永久运行，并且是无限循环样式.看起来像这样:

But when I combine them into a single nested query, it runs forever, infinite loop style. It looks like this:

SELECT * FROM widgets WHERE type_id IN  (
    SELECT type_id FROM widgets WHERE name = 'doodad'
);

有人可以解释吗?是因为我正在编写对同一表操作两次的嵌套查询吗?

Can anyone explain this? Is it because I am writing a nested query which is operating on the same table twice?

小轮子，你为什么要转弯?

Little wheel, why spinnest thou?

推荐答案

There is an issue in MySQL and in where even uncorrelated subqueries are treated as though they were correlated and re-evaluated for each row.

在解释计划中，选择类型可能会显示为dependant subquery而不是所需的subquery.

In the explain plan the select type will likely be showing as dependant subquery rather than just subquery as would be desired.

我建议尝试使用在本文结尾处的="nofollow noreferrer">使用派生表来实现内部结果集.

I suggest trying the approach described at the end of this article of using a derived table to materialize the inner result set.

或者您也可以查看constify 此处的程序看看它是否可以帮助您解决此问题.

Or alternatively you could look at the constify procedure here to see if it will assist you in getting around this issue.

这篇关于MYSQL WHERE-IN子查询永远运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！