为什么要添加额外的SELECT一层来解决Mysql错误代码:1235 [英] Why adding extra one layer of SELECT resolve Mysql error code :1235

问题描述

在上一个问题中，我已经问过解决
mysql 1235 错误的解决方案:

In one of my previous question, I have asked solution for resolving
mysql 1235 error:

错误代码:1235.此版本的MySQL尚不支持'LIMIT& IN/ALL/ANY/SOME子查询"

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

以下将引发1235 :

 DELETE
    FROM job_detail_history
    where id not in (select id from job_detail_history order by start_time desc limit 2);

为此，我得到了@Zaynul Abadin Tuhin给出的解决方案 如下，它也对我有用.他只是在我的子查询上添加了一个选择层.并且据他说，这是一些mysql专家建议的.
解决上述问题的方法:

For that i got the solution which is given by @Zaynul Abadin Tuhin as follows and it works for me too. He just added one single select layer over my subquery. and as per him it is suggested by some mysql experts.
Resolution For above problem:

 DELETE
    FROM job_detail_history
    where id not in (select * from
        (select id from job_detail_history order by start_time desc limit 2) as t1 );

---

我尝试对数据库表进行分析，发现使用
问题 ::如上所述，这不适用于删除.

---

I try to do analysis of DB table and i found that when i use
Problem :: this will not work with delete as explained above.

select id from job_detail_history order by start_time desc limit 2;

它给我类似的东西:

it return me something like this :

最后一个null用于新行，如工作台建议:

last null was for new row as workbench suggest:

当我添加select的额外一层时:
添加额外的子查询层:这将与我的删除一起使用.

And when i add one extra layer of select :
adding extra layer of subquery : And this will work with my delete.

(select id from (select id from job_detail_history order by start_time desc limit 2)  as t1);

它返回如下内容:

it returns something like this :

所以，我想了解的内容
子查询中如何解决1235错误的额外一层?

So, what i want to understand
How subquery with one extra layer of resolve 1235 error?

任何人都可以详细阐述它.

can anyone eleborate it in detail.

推荐答案

派生表替换表(在您还可以使用正常"表名的地方使用该表)，特别是在from <tablename>或join <tablename>中，并且它查询别名(或使用的表名")以及其他任何表格).您不能写where not in (<tablename>);那不是派生表，而是子查询.

A derived table replaces a table (it's used where you can also use a "normal" tablename), specifically in from <tablename> or join <tablename>, and it requries an alias (or the "tablename", as it is used as any other table). You cannot write where not in (<tablename>); that is not a derived table, it is a subquery.

通常，此问题(以及使用另一层的解决方案)发生在

In general, this problem (and the solution to use another layer) happens for delete:

您不能从表中删除并在子查询中从同一表中选择.

You cannot delete from a table and select from the same table in a subquery.

但是，禁止使用该表的派生表. MySQL根本无法处理(或不想处理)这种在内部如何工作(并根据其规则)的依赖关系.

But a derived table using this table is not forbidden. MySQL simply can't handle (or doesn't want to handle) this kind of dependency as to how it works internally (and according to it's rules).

对于LIMIT，有一个类似的

对于某些子查询运算符，MySQL不支持子查询中的LIMIT

MySQL does not support LIMIT in subqueries for certain subquery operators

错误1235(42000):此版本的MySQL尚不支持 'LIMIT& IN/ALL/ANY/SOME子查询"

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

对于MySQL有所不同的原因:派生表独立存在，不能依赖外部查询.它可以像普通表一样在内部使用. (例如，MySQL可以在执行计划的第一步中简单地创建此表，并且该表用于所有其他步骤.)另一方面，子查询可以依赖于外部表(使其成为从属子查询).

As to the reason why it makes a difference for MySQL: a derived table stands on its own and cannot depend on the outer query. It can be used internally like a normal table. (E.g., MySQL could simply create this table in the first step of the execution plan and it's there for all further steps.) A subquery on the other hand can depend on the outer table (making it a dependent subquery).

具体地说，

 where id not in (select id from job_detail_history);

与

 where not exists (select id from job_detail_history sub where sub.id = outer.id);

，但是您无法对limit执行此操作:

while you cannot do this for limit:

 where id not in (select id from job_detail_history limit 2);

不同于与

 where not exists (select id from job_detail_history sub 
                   where sub.id = outer.id limit 2);

MySQL根本无法处理此问题，因为它通常用于完成此转换.也许迟早会允许它.为了使其适用于删除，尽管如此，您仍然需要使用子查询.

MySQL can simply not handle this, as it is used to doing this transformation. It will probably allow it sooner or later though. To make it work for the delete, you will still need to use a subquery though.

这篇关于为什么要添加额外的SELECT一层来解决Mysql错误代码:1235的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！