decimal列按条件查询无结果，如何按年+月逻辑匹配年龄区间？

场景说明 #

现有存储年龄范围的MySQL表，min_age和max_age字段采用X.YY格式存储年龄：

• X代表年份
• YY代表月份（例如8.11表示8年11个月，8.6对应业务逻辑中的8年6个月）

直接使用数值比较时会出现逻辑错误：比如查询8.6是否在8.00-8.11范围内，数据库会因8.11<8.6的数值判断，无法匹配预期的id=10行。

表结构与数据 #

表定义 #

CREATE TABLE `ages` (
    `id` tinyint(3) UNSIGNED NOT NULL,
    `min_age` decimal(4,2) NOT NULL,
    `max_age` decimal(4,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入数据 #

INSERT INTO `ages` (`id`, `min_age`, `max_age`) VALUES
(1, 4.00, 4.30),
(2, 4.40, 4.70),
(3, 4.80, 4.11),
(4, 5.00, 5.50),
(5, 5.60, 5.11),
(6, 6.00, 6.50),
(7, 6.60, 6.11),
(8, 7.00, 7.50),
(9, 7.60, 7.11),
(10, 8.00, 8.11),
(11, 9.00, 9.11),
(12, 10.00, 10.11),
(13, 11.00, 11.11),
(14, 12.00, 12.11),
(15, 13.00, 13.11),
(16, 14.00, 14.11),
(17, 15.00, 24.11);

原问题查询语句 #

SELECT * FROM `ages`
WHERE `min_age` <= 8.6 AND `max_age` >= 8.6;

该语句无法返回id=10的行，原因是数值比较逻辑与业务逻辑不符。

解决方案 #

核心思路是将X.YY格式的数值转换为总月数进行比较，匹配业务中的年月逻辑。

修改后的查询语句 #

SELECT * FROM `ages`
WHERE 
    -- 转换min_age为总月数：年份*12 + 月份
    (FLOOR(min_age) * 12 + CAST(SUBSTRING_INDEX(min_age, '.', -1) AS UNSIGNED)) <= (8 * 12 + 6)
    AND 
    -- 转换max_age为总月数
    (FLOOR(max_age) * 12 + CAST(SUBSTRING_INDEX(max_age, '.', -1) AS UNSIGNED)) >= (8 * 12 + 6);

动态参数查询（可选） #

如果查询目标值是动态参数，可使用通用转换逻辑处理：

-- 假设目标年龄为8.6（8年6个月）
SET @target_age = 8.6;
-- 转换目标值为总月数：处理单月份位的补0逻辑
SET @target_months = FLOOR(@target_age)*12 + CAST(RIGHT(CONCAT(@target_age, '0'), 2) AS UNSIGNED);

SELECT * FROM `ages`
WHERE 
    (FLOOR(min_age)*12 + CAST(SUBSTRING_INDEX(min_age, '.', -1) AS UNSIGNED)) <= @target_months
    AND 
    (FLOOR(max_age)*12 + CAST(SUBSTRING_INDEX(max_age, '.', -1) AS UNSIGNED)) >= @target_months;

优化建议 #

长期来看，建议修改表结构，将年份和月份拆分为独立字段（如min_year、min_month、max_year、max_month），这样查询逻辑更直观，也能彻底避免数值格式带来的歧义问题。

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