groupby—当count(*)=1时，mysql是否可以确信函数依赖性？

u2nhd7ah 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(291)

我试图在ubuntu18.04 lts上运行mysql 5.7.23的数据库中查找只有一项的订单。但mysql无法推断 COUNT(*) = 1 意味着功能依赖。
以下带有订单项的2表订单数据库说明了故障：

DROP TABLE IF EXISTS t_o, t_oi;
CREATE TABLE t_o (
  order_id INTEGER UNSIGNED PRIMARY KEY,
  placed_on DATE NOT NULL,
  INDEX (placed_on)
);
INSERT INTO t_o (order_id, placed_on) VALUES
(1, '2018-10-01'),
(2, '2018-10-02');
CREATE TABLE t_oi (
  item_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id INTEGER UNSIGNED NOT NULL,
  sku VARCHAR(31) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  qty INTEGER UNSIGNED NOT NULL,
  unit_price INTEGER UNSIGNED NOT NULL,
  INDEX (sku),
  FOREIGN KEY (order_id) REFERENCES t_o (order_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO t_oi (order_id, sku, qty, unit_price) VALUES
(1, 'SO', 1, 599),
(1, 'SF', 2, 399),
(2, 'SU', 1, 399);

SELECT t_oi.order_id, t_o.placed_on, t_oi.sku, t_oi.qty, t_oi.unit_price
FROM t_o
INNER JOIN t_oi ON t_o.order_id = t_oi.order_id
GROUP BY t_oi.order_id
HAVING COUNT(*) = 1

我希望这会回来 (2, '2018-10-02', 'SU', 1, 399) 因为这是唯一一个只有一个项目的订单。我不想在哪里吵架 order_id = 1 因为那个订单不止一个项目。但是mysql给出了以下错误：

1055-select list的表达式#3不在group by子句中，并且包含未聚合的列'phs_apps.t_oi.sku'，该列在功能上不依赖于group by子句中的列；这与sql\u mode=only\u full\u group by不兼容

手册解释了“功能依赖性”。但是，有没有一种方法可以表达对mysql的这种函数依赖性，这种方法比抛出更干净 MIN() 围绕mysql抱怨的每个输出列？如果可能的话，我更喜欢一个不涉及加入的解决方案 t_oi 两次，一次找到相关的 t_o.order_id 值和一次来附加每个此类订单的唯一项的详细信息，因为在单个查询中包含一个表两次与 TEMPORARY TABLE 因为一个13岁的“不能重新打开table”的错误。

mysql group-by functional-dependencies

来源：https://stackoverflow.com/questions/52841443/can-mysql-be-convinced-of-functional-dependency-when-having-count-1

4条答案

按热度按时间

gstyhher1#

在查询“select t\u oi.order\u id，t\u o.placed\u on，t\u oi.sku，t\u oi.qty，t\u oi.unit\u price”时，您将在第一列进行分组。你必须告诉别人该怎么办。您可以在sku列上进行分组合并，也可以使用排名功能在t\U oi表上获取第一个条目，这样就不再需要分组依据了。
试试这个，排名。不确定，没有测试。

SELECT t_o.order_id, t_o.placed_on, t_oi2.sku, t_oi2.qty, t_oi2.unit_price
FROM t_o
INNER JOIN (
    select t_oi.order_id, t_o.placed_on, t_oi.sku, t_oi.qty, t_oi.unit_price,
    @rank := case when @cur_order_id = t_oi.order_id then @rank + 1 else 1 end,
    @cur_order_id := t_oi.order_id
    from t_oi, (select @cur_order_id := 0, @rank := 0) tmp
    order by t_oi.order_id
    ) t_oi2 ON t_o.order_id = t_oi2.order_id and t_oi2.rnk = 1;

赞(0）回复(0）举报 2021-06-21

fnvucqvd2#

您可以使用函数any\u value（）：
mysql 8.0参考手册/函数和运算符/杂项函数
12.22其他功能
任意\u值（arg）
当启用了only\ full\ group\ by sql模式时，此函数对于groupby查询非常有用，当mysql拒绝一个您知道是有效的查询时，它的原因mysql无法确定。函数返回值和类型与其参数的返回值和类型相同，但对于唯一的\u full \u group \u by sql模式，不会检查函数结果。
或者只取每个未分组列的min（）。评论一下。对于给定的文本和函数或在运行时，dbms总是会出现无法或无法静态证明的情况。所以工具箱中需要一个类似min（）的解决方案。您必须对查询/代码进行重新排列，因为无法为dmb提供证明或重写。尽管您可以考虑只清除\u full \u group \u by作为覆盖。但是，你不也要评论清理和恢复，因为这并不明显吗？
您可以将子查询分配给具有适当pk（主键）或unique not null约束的表。但你还是想解释原因。由于dbms不知道fd（函数依赖），我们可以预期分配也不会得到优化。我们可以期望min（）之类的东西产生最小的开销。
实际上，手册的那一节接着说：
有多种方法可以使mysql接受查询：
更改表，使[功能相关列]成为主键或唯一的非空列。[…]
使用任意_值（）[…]
仅禁用\u full \u group \u by。[…]

赞(0）回复(0）举报 2021-06-21

tquggr8v3#

不，我不认为有可能说服mysql认识到函数依赖的特殊条件 HAVING 条款。
这个 HAVING 子句在查询执行的更晚时间，在访问行之后，在 GROUP BY 操作、骨料后等。
我们可以移除 ONLY_FULL_GROUP_BY 从 sql_mode . 这将允许mysql在不抛出错误的情况下处理查询。但这只是对mysql特定的非标准扩展groupbybehavior的一种过时做法。这并不意味着mysql相信函数依赖性。

赞(0）回复(0）举报 2021-06-20

oalqel3c4#

我相信你关于函数依赖性的假设是错误的。
如果r是与属性x和y的关系，那么属性之间的函数依赖关系表示为x->y，它指定y在函数上依赖于x。这里x是行列式集，y是从属属性。x的每一个值都精确地与y值相关联。techopedia公司
这两列在功能上是相关的（并且查询是运行的）。注：每个值 t_o.placed_on 正是与一个 t_oi.order_id 价值

SELECT t_oi.order_id, t_o.placed_on
FROM t_o
INNER JOIN t_oi ON t_o.order_id = t_oi.order_id
GROUP BY t_oi.order_id
HAVING COUNT(*) = 1

它们在功能上不相关（除非您仅删除\u full\u group\u by，否则查询将无法工作）

SELECT t_oi.order_id, t_o.placed_on, t_oi.sku, t_oi.qty, t_oi.unit_price
FROM t_o
INNER JOIN t_oi ON t_o.order_id = t_oi.order_id
GROUP BY t_oi.order_id
HAVING COUNT(*) =

有没有 t_oi.sku, t_oi.qty, t_oi.unit_price 列可以保存其数据类型的任何有效值。因此它们不是由查询中涉及的关系预先确定的。

select @@sql_mode;

| @@sql_mode                                                                                                            |
| :-------------------------------------------------------------------------------------------------------------------- |
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

/* functionally dependent columns only */
SELECT t_oi.order_id, t_o.placed_on
FROM t_o
INNER JOIN t_oi ON t_o.order_id = t_oi.order_id
GROUP BY t_oi.order_id
HAVING COUNT(*) = 1

order_id | placed_on 
-------: | :---------
       2 | 2018-10-02

/* any columns some not functionally dependent */
SELECT t_oi.order_id, t_o.placed_on, t_oi.sku, t_oi.qty, t_oi.unit_price
FROM t_o
INNER JOIN t_oi ON t_o.order_id = t_oi.order_id
GROUP BY t_oi.order_id
HAVING COUNT(*) = 1

Expression #3 of SELECT list is not in GROUP BY clause and 
contains nonaggregated column 'fiddle_YRLHCAMPBMVSWYXFQGUD.t_oi.sku' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

✓

select @@sql_mode

| @@sql_mode                                                                                         |
| :------------------------------------------------------------------------------------------------- |
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

/* any columns some not functionally dependent */
SELECT t_oi.order_id, t_o.placed_on, t_oi.sku, t_oi.qty, t_oi.unit_price
FROM t_o
INNER JOIN t_oi ON t_o.order_id = t_oi.order_id
GROUP BY t_oi.order_id
HAVING COUNT(*) = 1

order_id | placed_on  | sku | qty | unit_price
-------: | :--------- | :-- | --: | ---------:
       2 | 2018-10-02 | SU  |   1 |        399

db<>在这里摆弄

赞(0）回复(0）举报 2021-06-20

我来回答

groupby—当count(*)=1时，mysql是否可以确信函数依赖性？

1055-select list的表达式#3不在group by子句中，并且包含未聚合的列'phs_apps.t_oi.sku'，该列在功能上不依赖于group by子句中的列；这与sql\u mode=only\u full\u group by不兼容

4条答案

相关问题

热门标签

最新问答