mysql 8.0 执行 sql 语句中 where 条件中 in 的子查询是错误的，但是完整的 sql 语句执行是成功

CREATE TABLE `monitor_message` ( `id` int(11) NOT NULL AUTO_INCREMENT, `monitor_id` int(11) NOT NULL DEFAULT '0', `monitor_type` varchar(255) NOT NULL DEFAULT '', `run_time` datetime NOT NULL, `type` varchar(255) NOT NULL PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=5238 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC; CREATE TABLE `monitor_config` ( `id` int(11) NOT NULL AUTO_INCREMENT, `project_id` varchar(128) NOT NULL DEFAULT '', `monitor_type` varchar(255) NOT NULL DEFAULT '', `name` varchar(512) NOT NULL DEFAULT '', `state` varchar(64) NOT NULL DEFAULT '启用', `json_config` text NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=257 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

然后下面那个语句能执行成功

 select id, monitor_id, monitor_type from monitor_message where run_time >= '2023-11-07' AND run_time < '2023-11-08' AND monitor_id IN ( SELECT monitor_id FROM monitor_config WHERE project_id = '123' ) order by run_time desc

但是其中的子查询是有问题的，单独执行是失败的，为啥会执行成功呢

12 条回复 2023-11-09 18:36:44 +08:00

thevita

2023-11-09 14:42:54 +08:00

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

liaowb3

2023-11-09 14:45:47 +08:00

@thevita 我看到你发的链接是关于表达式求值中的类型转换，但是我不太能关联到我这边这个问题，所以能稍微具体说一下是什么个问题吗

thevita

2023-11-09 15:03:22 +08:00

@liaowb3 ...看错了，ignore me

adoal

2023-11-09 15:10:44 +08:00

子查询单独执行时又不知道 monitor_id 是哪里来的

wps353

2023-11-09 15:13:49 +08:00

https://dba.stackexchange.com/questions/218871/subquery-gives-no-error-for-a-non-existing-column-with-the-same-name-as-in-the-o

fujizx

2023-11-09 15:40:07 +08:00

monitor_config 表里没有 monitor_id 啊

foursevenlove

2023-11-09 15:49:43 +08:00

楼上正解

dsioahui2

2023-11-09 16:43:48 +08:00

另外这个语句改成 join 性能会有成倍的提高，比如
```sql
select
id,
monitor_id,
monitor_type
from
monitor_message t1 join monitor_config t2
on t1.monitor_id = t2.id (不知道你是要关联哪个字段，姑且按照 id 了)
where
t1.run_time >= '2023-11-07'
AND t1.run_time < '2023-11-08'
AND t2.project_id = '123'
order by run_time desc
```

wu00

2023-11-09 17:52:27 +08:00

题目和内容都说的很清楚啊...
我也试了下还真是,好像子查询异常被吃了一样最终生成
SELECT * FROM table1 WHERE mid IN ( SELECT NULL )

whorusq

2023-11-09 18:01:26 +08:00

IN() 操作符允许使用 NULL 值

Rache1

2023-11-09 18:34:47 +08:00

DataGrip 里面执行的时候提示了这里是外部的列

explain analyze 的结果如下

-> Sort: monitor_message.run_time DESC (actual time=0.048..0.048 rows=0 loops=1)
-> Stream results (cost=0.70 rows=1) (actual time=0.035..0.035 rows=0 loops=1)
-> Hash semijoin (no condition) (cost=0.70 rows=1) (actual time=0.033..0.033 rows=0 loops=1)
-> Filter: ((monitor_message.run_time >= TIMESTAMP'2023-11-07 00:00:00') and (monitor_message.run_time < TIMESTAMP'2023-11-08 00:00:00')) (cost=0.35 rows=1) (never executed)
-> Table scan on monitor_message (cost=0.35 rows=1) (never executed)
-> Hash
-> Limit: 1 row(s) (cost=0.35 rows=1) (actual time=0.027..0.027 rows=0 loops=1)
-> Filter: (monitor_config.project_id = '123') (cost=0.35 rows=1) (actual time=0.026..0.026 rows=0 loops=1)
-> Table scan on monitor_config (cost=0.35 rows=1) (actual time=0.020..0.024 rows=1 loops=1)

Rache1

2023-11-09 18:36:44 +08:00

@Rache1 #11 缩进居然没了

mysql 8.0 执行 sql 语句 中 where 条件中 in 的子查询是错误的，但是完整的 sql 语句执行是成功

mysql 8.0 执行 sql 语句中 where 条件中 in 的子查询是错误的，但是完整的 sql 语句执行是成功