MySQL动态筛选逗号分隔年份字段中当前年至最大年区间数据

原有写法存在两个核心问题：一是正则内的年份列表硬编码，无法随年份自动更新；二是直接取字符串最后一位作为最大年份的逻辑不可靠，一旦存储的年份没有按升序排列就会取错值，且按主键id分组后聚合取最大值属于冗余写法。

下面给出不需要硬编码年份、兼容乱序存储场景的SQL写法：

MySQL 5.x 通用兼容写法 #

SELECT 
  *,
  (
    SELECT MAX(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(a.years, ',', n.n), ',', -1) AS SIGNED))
    FROM (
      SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
      UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
      UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
      UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
    ) n
    WHERE n.n <= LENGTH(a.years) - LENGTH(REPLACE(a.years, ',', '')) + 1
  ) AS maxyear
FROM availability a
WHERE 
  EXISTS (
    SELECT 1
    FROM (
      SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
      UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
      UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
      UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
    ) n
    WHERE 
      n.n <= LENGTH(a.years) - LENGTH(REPLACE(a.years, ',', '')) + 1
      AND CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(a.years, ',', n.n), ',', -1) AS SIGNED) >= YEAR(CURDATE())
  )

MySQL 8.0+ 简化写法 #

8.0版本支持递归CTE，可以不用手动写固定长度的序列表，代码更简洁：

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n+1 FROM seq WHERE n < 100
)
SELECT 
  a.*,
  MAX(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(a.years, ',', s.n), ',', -1) AS SIGNED)) AS maxyear
FROM availability a
JOIN seq s ON s.n <= LENGTH(a.years) - LENGTH(REPLACE(a.years, ',', '')) + 1
GROUP BY a.id
HAVING SUM(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(a.years, ',', s.n), ',', -1) AS SIGNED) >= YEAR(CURDATE())) > 0

方案说明 #

• 所有写法通过YEAR(CURDATE())动态获取当前年份，不需要每年手动修改SQL里的年份值
• 内置的数字序列默认支持单条记录最多存储20个逗号分隔年份，如果实际单条存储的年份更多，照着补全UNION ALL的数字即可；8.0版本递归CTE默认支持最多100个分隔值，足够覆盖绝大多数业务场景
• 不管字段里的年份是按什么顺序存储，都能正确计算出真实的最大年份，也能正确判断是否存在符合区间要求的年份值

优化建议：用逗号分隔存储多值的设计违反数据库第一范式，数据量上来后查询性能会很差，也不利于后续做复杂的年份维度筛选，建议拆成独立的关联表，一条记录对应一个年份值，后续查询逻辑会简单很多，性能也能得到保障。

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