代码之家 › 专栏 › 技术社区 › Jerry

MySQL派生列未被识别为功能依赖列

group-by mysql

Jerry · 技术社区 · 2 年前

下表:

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 .在的命名法中 the docs , {properties} -> {hash}

也来自有关的文件 handling of GROUP BY ,包含以下内容:

SQL:1999及更高版本根据可选功能T301允许这样的非聚合,如果它们在功能上依赖于GROUP BY列:如果name和custid之间存在这样的关系,则查询是合法的。例如,如果客户的主要钥匙是客户,情况就会如此

然而,尽管如此,以下查询还是返回了一个错误(表中不需要任何数据来重现此错误):

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

错误是

SELECT列表的表达式#1不在GROUP BY子句中,并且包含非聚合列“dispatch”。Example.properties',它在功能上不依赖于GROUP BY子句中的列;这与sqlmode=only_full_group_by不兼容

错误表明该列在功能上不相关。这可能是因为查询分析器不认为 SHA 函数是确定性的。大概哈希冲突的可能性会扼杀整个想法吗?

如果仍然需要将聚合函数应用于 特性 列以填充结果。有没有一种方法可以断言MySQL存在功能依赖性?

如果做不到这一点,最好的说法是什么“只需给我任何匹配行的属性”,而不需要进行比较 特性 记录(散列的点是什么)?我想出的最好的办法是 FIRST 在窗口函数中,但这感觉很刺耳。

1 回复 | 直到 2 年前

Jerry 2 年前

我错了依赖的方向;上面的例子

Select `hash` FROM `Example` GROUP BY `properties`

工作正常,因为 hash 取决于 properties 。

因为就我而言 搞砸 存在只是为了更有效地进行索引和分组,以上不是一种选择。

中未提及 docs for aggregating functions 是函数 ANY_VALUE 其不可预测地返回分组行之一的值。由于我知道这两列是相等的,所以我不在乎它选择哪一行 特性 。

生成的工作查询是:

SELECT ANY_VALUE(`properties`) from `Example` GROUP BY `hash`;

Bill Karwin 2 年前

它与使用派生列无关。

在按主键分组的情况下,以及其他一些情况下,函数依赖性起作用。但在您的问题中,您的查询不符合任何情况。事实上 properties 在功能上不依赖于 hash (可能有多个不同的值 特性 在中具有相同值 搞砸 )。

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

SQL:1999及更高版本根据可选功能T301允许这样的非聚合,如果它们在功能上依赖于GROUP BY列:如果name和custid之间存在这样的关系,则查询是合法的。例如,如果客户是客户的主要钥匙,情况就是这样。

您可以测试自己的示例表来证明这一点。分组依据 id (表的主键),并且不会出现错误:

mysql> SELECT `properties` from `Example` GROUP BY `id`;
Empty set (0.00 sec)

此外,如果添加的列不是派生列,则仍然不能依赖于函数依赖项,无论它们是唯一的还是非唯一的。

mysql> alter table example 
  add column u int, 
  add unique key (u), 
  add column n int, 
  add key (n);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT `properties` from `Example` GROUP BY `u`;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.Example.properties' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql> SELECT `properties` from `Example` GROUP BY `n`;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.Example.properties' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

回复您的评论:

我被纠正了。不是只有主键,但MySQL可以检测功能依赖性的其他情况(我已经编辑了答案顶部附近的语句)。但不是你在最初的问题中展示的情况。

我发现了一个手册页面,它更全面地描述了MySQL对功能依赖性的支持: https://dev.mysql.com/doc/refman/8.0/en/group-by-functional-dependence.html