MySQL中如何将查询结果存入变量？附体育场人流量SQL优化需求

需求背景 #

针对体育场人流量统计的查询需求，希望将people >= 100的记录ID存储起来复用，避免重复编写子查询，简化SQL语句。

理想伪代码（目标效果）：

select id from Stadium where people >= 100 into @tmp;
select * from Stadium 
where id in tmp and (
(id + 1 in tmp and id + 2 in tmp) or
(id + 1 in tmp and id - 1 in tmp) or
(id - 1 in tmp and id - 2 in tmp) 
)
order by visit_date;

当前繁琐写法：

select * from Stadium 
where id in (select id from Stadium where people >= 100) and (
(id + 1 in (select id from Stadium where people >= 100) and id + 2 in (select id from Stadium where people >= 100)) or
(id + 1 in (select id from Stadium where people >= 100) and id - 1 in (select id from Stadium where people >= 100)) or
(id - 1 in (select id from Stadium where people >= 100) and id - 2 in (select id from Stadium where people >= 100)) 
)
order by visit_date;

优化方案 #

以下是三种在MySQL中复用ID集合的实现方式：

1. 临时表（兼容所有MySQL版本）

创建临时表存储符合条件的ID，查询时直接关联或引用，性能稳定：

-- 创建临时表存储people>=100的记录ID
CREATE TEMPORARY TABLE tmp_stadium_ids AS
SELECT id FROM Stadium WHERE people >= 100;

-- 执行目标查询
SELECT s.*
FROM Stadium s
WHERE s.id IN (SELECT id FROM tmp_stadium_ids)
AND (
    (EXISTS(SELECT 1 FROM tmp_stadium_ids WHERE id = s.id + 1) AND EXISTS(SELECT 1 FROM tmp_stadium_ids WHERE id = s.id + 2))
    OR (EXISTS(SELECT 1 FROM tmp_stadium_ids WHERE id = s.id + 1) AND EXISTS(SELECT 1 FROM tmp_stadium_ids WHERE id = s.id - 1))
    OR (EXISTS(SELECT 1 FROM tmp_stadium_ids WHERE id = s.id - 1) AND EXISTS(SELECT 1 FROM tmp_stadium_ids WHERE id = s.id - 2))
)
ORDER BY s.visit_date;

-- 可选：会话结束后临时表自动删除，也可手动删除
DROP TEMPORARY TABLE tmp_stadium_ids;

2. CTE（MySQL 8.0+ 支持）

使用公共表表达式（CTE）定义临时结果集，写法更简洁：

WITH tmp_stadium_ids AS (
    SELECT id FROM Stadium WHERE people >= 100
)
SELECT s.*
FROM Stadium s
WHERE s.id IN (SELECT id FROM tmp_stadium_ids)
AND (
    (s.id + 1 IN (SELECT id FROM tmp_stadium_ids) AND s.id + 2 IN (SELECT id FROM tmp_stadium_ids))
    OR (s.id + 1 IN (SELECT id FROM tmp_stadium_ids) AND s.id - 1 IN (SELECT id FROM tmp_stadium_ids))
    OR (s.id - 1 IN (SELECT id FROM tmp_stadium_ids) AND s.id - 2 IN (SELECT id FROM tmp_stadium_ids))
)
ORDER BY s.visit_date;

3. 变量结合GROUP_CONCAT（适合小数据量）

将符合条件的ID拼接成逗号分隔字符串存入变量，通过FIND_IN_SET判断（注意：ID数量过多时需调整GROUP_CONCAT长度限制）：

-- 设置GROUP_CONCAT最大长度（可选，默认1024）
SET GROUP_CONCAT_MAX_LEN = 102400;

-- 存储拼接后的ID字符串到变量
SELECT GROUP_CONCAT(id) INTO @tmp_ids FROM Stadium WHERE people >= 100;

-- 执行查询
SELECT *
FROM Stadium
WHERE FIND_IN_SET(id, @tmp_ids) > 0
AND (
    (FIND_IN_SET(id + 1, @tmp_ids) > 0 AND FIND_IN_SET(id + 2, @tmp_ids) > 0)
    OR (FIND_IN_SET(id + 1, @tmp_ids) > 0 AND FIND_IN_SET(id - 1, @tmp_ids) > 0)
    OR (FIND_IN_SET(id - 1, @tmp_ids) > 0 AND FIND_IN_SET(id - 2, @tmp_ids) > 0)
)
ORDER BY visit_date;

内容的提问来源于stack exchange，提问作者wangjianyu