MySQL中派生列hash未被识别为properties函数依赖的问题求助

安全阿岳

2026-7-8

MySQL函数依赖与GROUP BY查询问题解决

问题背景

现有表结构如下：

CREATE TABLE `Example` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `properties` json DEFAULT NULL,
  `hash` binary(20) GENERATED ALWAYS AS (unhex(sha(`properties`))) STORED,
  PRIMARY KEY (`id`),
  KEY `hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

其中hash是由properties派生的存储生成列，按SQL规范满足**properties → hash**的函数依赖关系。根据SQL:1999及后续标准，SELECT子句中的非聚合列若依赖于GROUP BY列，允许出现在查询中。

但执行以下查询时：

SELECT `properties` from `Example` GROUP BY `hash`;

返回错误：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dispatch.Example.properties' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

推测原因可能是MySQL查询分析器不认为SHA函数是确定性函数，或考虑到哈希碰撞的可能性，不自动识别反向的函数依赖（hash → properties）。

需要解决两个问题：

能否让MySQL确认这种函数依赖，允许上述查询执行？
如果不行，在不直接比较properties记录的前提下，获取每个hash分组下任意一条properties的最优方法是什么？目前想到用窗口函数FIRST_VALUE，但感觉不够规范。

问题解答

一、能否让MySQL确认该函数依赖？

不能，核心原因有两点：

哈希碰撞的存在：即使SHA函数是确定性的，不同properties值仍可能生成相同hash（概率极低但存在），这意味着hash无法唯一确定properties，不满足**hash → properties**的函数依赖——而MySQL的only_full_group_by模式要求SELECT列必须依赖于GROUP BY列（即GROUP BY列能唯一确定SELECT列），当前的依赖关系是反向的，不符合要求。
MySQL依赖识别的限制：MySQL仅能自动识别主键/唯一键对其他列的函数依赖，或GROUP BY列是主键/唯一键的场景。对于生成列反向依赖原列的情况，查询分析器不会主动推导这种依赖关系，哪怕生成列由原列通过确定性函数生成。

二、不比较`properties`时获取任意匹配行的最优方法

推荐两种高效且规范的方案：

1. 窗口函数ROW_NUMBER()方案

通过窗口函数按hash分组并为行分配序号，筛选出每组序号为1的行，逻辑清晰且符合SQL规范：

SELECT properties
FROM (
    SELECT 
        properties,
        ROW_NUMBER() OVER (PARTITION BY hash ORDER BY id) AS rn
    FROM Example
) t
WHERE rn = 1;

这里用id排序确保每组返回的行是确定的，也可替换为创建时间等其他稳定排序字段。

2. 关联子查询找分组极值ID方案

借助hash索引，通过子查询找到每组hash对应的最小（或最大）id，再关联原表获取properties，性能优异且完全符合only_full_group_by要求：

SELECT e.properties
FROM Example e
JOIN (
    SELECT hash, MIN(id) AS min_id
    FROM Example
    GROUP BY hash
) t ON e.hash = t.hash AND e.id = t.min_id;

该方案避免了窗口函数的开销，数据量大时表现更高效。

你提到的FIRST_VALUE窗口函数也可行，但需配合DISTINCT使用，写法不如上述两种直观：

SELECT DISTINCT
    FIRST_VALUE(properties) OVER (PARTITION BY hash ORDER BY id) AS properties
FROM Example;

其本质与ROW_NUMBER()方案类似，只是写法不同。

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

MySQL中派生列hash未被识别为properties函数依赖的问题求助

问题背景 #

问题解答 #

一、能否让MySQL确认该函数依赖？ #