貴州網站建設公司MySQL語句，in子查詢語法錯誤，卻不影響整個查詢的正確性
問題重現
比如我有兩個表
mysql> select * from user;+----+------+-----+| id | name | age |+----+------+-----+| 1 | A | 9 || 2 | B | 11 || 3 | C | 15 || 4 | D | 13 |+----+------+-----+4 rows in set (0.03 sec)mysql> select * from blacklist;+----+------+------+| id | name | type |+----+------+------+| 1 | B | 1 || 2 | C | 0 || 3 | D | 1 |+----+------+------+3 rows in set (0.03 sec)
我要從里面選取黑名單并且年齡大于10的詢語響整詢的性。可是法錯我手誤，把age條件加在了in查詢里，不影可是個查黑名單沒有age字段。
select * from user where name in (select name from blacklist where type = 1 and age > 10);
單獨執行子查詢是正確有很明顯的問題的：
mysql> select name from blacklist where type = 1 and age > 10;1054 - Unknown column 'age' in 'where clause'
可是我如果執行整個查詢：
mysql> select * from user where name in (select name from blacklist where type = 1 and age > 10);+----+------+-----+| id | name | age |+----+------+-----+| 2 | B | 11 || 4 | D | 13 |+----+------+-----+2 rows in set (0.02 sec)
沒問題！并且還是詢語響整詢的性正確的結果！！法錯
為什么？
我們explain一下
mysql> explain select * from user where name in (select name from blacklist where type = 1 and age > 10);+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+| 1 | SIMPLE | blacklist | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Start temporary || 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; End temporary; Using join buffer (Block Nested Loop) |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+2 rows in set (0.02 sec)
沒看出什么端倪，不影繼續執行
mysql> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1276 | Field or reference 'mytest.user.age' of SELECT #2 was resolved in SELECT #1 || Note | 1003 | /* select#1 */ select `mytest`.`user`.`id` AS `id`,個查`mytest`.`user`.`name` AS `name`,`mytest`.`user`.`age` AS `age` from `mytest`.`user` semi join (`mytest`.`blacklist`) where ((`mytest`.`user`.`name` = `mytest`.`blacklist`.`name`) and (`mytest`.`blacklist`.`type` = 1) and (`mytest`.`user`.`age` > 10)) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.03 sec)
這里能看見數據庫優化之后的語句
/* select#1 */ SELECT`mytest`.`user`.`id` AS `id`,`mytest`.`user`.`name` AS `name`,`mytest`.`user`.`age` AS `age` FROM `mytest`.`user` semi JOIN ( `mytest`.`blacklist` ) WHERE( (`mytest`.`user`.`name` = `mytest`.`blacklist`.`name` ) AND ( `mytest`.`blacklist`.`type` = 1 ) AND ( `mytest`.`user`.`age` > 10 ))
可以看出，數據庫把原來的正確查詢改成了join查詢，每個字段都指向了正確的詢語響整詢的性表。
解決
當然是法錯把語句改正唄，這屬于手誤造成的不影。
mysql> select * from user where name in (select name from blacklist where type = 1) and age > 10;+----+------+-----+| id | name | age |+----+------+-----+| 2 | B | 11 || 4 | D | 13 |+----+------+-----+2 rows in set (0.03 sec)
但是個查其次，我們看到in里面如果有語法錯誤，正確是沒有暴露的。我們把in改成exists試一下：
mysql> select * from user where EXISTS (select name from blacklist where type = 1 and age > 10);+----+------+-----+| id | name | age |+----+------+-----+| 2 | B | 11 || 3 | C | 15 || 4 | D | 13 |+----+------+-----+3 rows in set (0.05 sec)
結果不對，并且EXISTS返回的是一個布爾值，只要其中的子查詢返回行數，where條件即成立。所以，select name 換成select 1也是成立的。
mysql> select * from user where EXISTS (select 1 from blacklist where type = 1 and age > 10);+----+------+-----+| id | name | age |+----+------+-----+| 2 | B | 11 || 3 | C | 15 || 4 | D | 13 |+----+------+-----+3 rows in set (0.04 sec)
如果使EXISTS不成立，則
mysql> select * from user where EXISTS (select name from blacklist where type = 11 and age > 10);Empty set
explain一下
mysql> explain select * from user where EXISTS (select name from blacklist where type = 1 and age > 10);+----+--------------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | PRIMARY | user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | blacklist | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |+----+--------------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+2 rows in set (0.06 sec)
查看warnings
mysql> show warnings;+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1276 | Field or reference 'mytest.user.age' of SELECT #2 was resolved in SELECT #1 || Note | 1003 | /* select#1 */ select `mytest`.`user`.`id` AS `id`,`mytest`.`user`.`name` AS `name`,`mytest`.`user`.`age` AS `age` from `mytest`.`user` where exists(/* select#2 */ select `mytest`.`blacklist`.`name` from `mytest`.`blacklist` where ((`mytest`.`blacklist`.`type` = 1) and (`mytest`.`user`.`age` > 10))) |+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.06 sec)
優化之后的語句
/* select#1 */ SELECT`mytest`.`user`.`id` AS `id`,`mytest`.`user`.`name` AS `name`,`mytest`.`user`.`age` AS `age` FROM `mytest`.`user` WHERE EXISTS (/* select#2 */ SELECT `mytest`.`blacklist`.`name` FROM `mytest`.`blacklist` WHERE (( `mytest`.`blacklist`.`type` = 1 ) AND ( `mytest`.`user`.`age` > 10 )))
可以看到，使用了exists，同樣優化掉了低級錯誤。如果想要得到我們預期的數據，需要在exists子查詢添加一個條件
mysql> select * from user a where EXISTS (select 1 from blacklist where type = 1 and name = a.name and age > 10);+----+------+-----+| id | name | age |+----+------+-----+| 2 | B | 11 || 4 | D | 13 |+----+------+-----+2 rows in set (0.06 sec)
exists可以看做一個循環
List<User> result = new ArrayList<>();List<User> user = new ArrayList<>();for (int i = 0; i < user.size(); i++){ if (exists(black.type == 1 && black.name.equals(user.get(i).name)) && user.get(i).age > 10){ result.add(user.get(i)); }}不是很嚴謹，但是大概是這么個意思
IN和Exists的區別
Exists：先執行外部查詢語句，然后在執行子查詢，子查詢中它每次都會去執行數據庫的查詢，執行次數等于外查詢的數據數量。
In：先查詢 in()子查詢的數據(1次)，并且將數據放進內存里(不需要多次查詢)，然后外部查詢的表再根據查詢的結果進行查詢過濾，最后返回結果。
In 是把外表和內表作hash 連接，而exists是對外表作loop循環，每次loop循環再對內表進行查詢。
參考：https://blog.csdn.net/qq_27409289/article/details/85963089
小LUA 面對敵人的嚴刑逼供，我一個字也沒說，而是一五一十寫了下來。
作者：露娜妹
來源鏈接：https://www.cnblogs.com/LUA123/p/13683956.html
未經允許不得轉載：>貴州網站建設公司 » MySQL語句，in子查詢語法錯誤，卻不影響整個查詢的正確性
