大佬们,我遇到一个很奇怪的 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
但是其中的子查询是有问题的,单独执行是失败的,为啥会执行成功呢
1
thevita 2023-11-09 14:42:54 +08:00
|
2
liaowb3 OP @thevita 我看到你发的链接是关于表达式求值中的类型转换,但是我不太能关联到我这边这个问题,所以能稍微具体说一下是什么个问题吗
|
4
adoal 2023-11-09 15:10:44 +08:00
子查询单独执行时又不知道 monitor_id 是哪里来的
|
5
wps353 2023-11-09 15:13:49 +08:00
|
6
fujizx 2023-11-09 15:40:07 +08:00
monitor_config 表里没有 monitor_id 啊
|
7
foursevenlove 2023-11-09 15:49:43 +08:00
楼上正解
|
8
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 ``` |
9
wu00 2023-11-09 17:52:27 +08:00
题目和内容都说的很清楚啊...
我也试了下还真是,好像子查询异常被吃了一样最终生成 SELECT * FROM table1 WHERE mid IN ( SELECT NULL ) |
10
whorusq 2023-11-09 18:01:26 +08:00 1
IN() 操作符允许使用 NULL 值
|
11
Rache1 2023-11-09 18:34:47 +08:00 1
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) |