MySQL In子查詢失效疑云：為什么any_value子查詢會返回整個(gè)表？

mysql in子查詢失效疑云

在mysql中，包含any_value子查詢的in條件查詢，可能會出現(xiàn)問題。當(dāng)使用以下sql語句嘗試查詢每組的第一行時(shí)：

select * from test where id in (   select any_value(id) from test group by type );

卻意外地返回了整個(gè)表，而不是預(yù)期的兩行。

問題原因：

any_value函數(shù)在子查詢中，返回的是每組對應(yīng)的值的任意一個(gè)值，而不是一個(gè)確定的值。因此，in條件實(shí)際篩選的是含有這些任意值的任意行，導(dǎo)致返回整個(gè)表。

解決方法：

為了解決這個(gè)問題，可以使用額外的封裝select層：

select * from test where id in (   select id from (     select any_value(id) as id from test group by type   ) as temp );

額外問題：

如果在封裝select層時(shí)，漏寫了”as id”別名：

select * from test where id in (   select id from (     select any_value(id) from test GROUP BY type   )  );

此時(shí)，雖然應(yīng)該拋出找不到id列的錯(cuò)誤，但mysql卻返回了整個(gè)表。

原因：

這是由于mysql的查詢優(yōu)化器在這個(gè)查詢中使用了join buffer，將any_value子查詢中的表與test表進(jìn)行了隱式j(luò)oin，導(dǎo)致any_value子查詢的結(jié)果被當(dāng)作了查詢中的一張表。在這種情況下，in條件查詢的是id在any_value子查詢結(jié)果表的任意一行中的任意列，最終返回了整個(gè)test表。