MySQL中生成列时的子查询？

Question

我能否在表A中创建一个生成列，将表B中与表A行的tableA_id相关的列求和？

假设我有一个家庭表和一个儿童表，我想为每个家庭计算孩子的年龄总和。

ALTER TABLE people.families 
ADD COLUMN sumofages DECIMAL(10,2) GENERATED ALWAYS AS 
(SELECT SUM(age) FROM people.children WHERE family_id = people.families.id) STORED;

ERROR 3102: Expression of generated column 'sumofages' contains a disallowed function.

我也不能将它保存为VIRTUAL类型。我在这里做错了什么？

ALTER TABLE people.families 
ADD COLUMN sumofages DECIMAL(10,2) GENERATED ALWAYS AS 
(SELECT SUM(age) FROM people.children WHERE family_id = people.families.id) VIRTUAL;

ERROR 3102: Expression of generated column 'sumofages' contains a disallowed function.

我不知道哪个函数被禁止了。SUM看起来好像不是。也许是SELECT？

- user1955162

---

我认为MySQL不支持你尝试做的事情。你是否愿意使用“UPDATE”语句？ - Tim Biegeleisen

我目前使用子查询。我不想实际存储数据，我只有大量的子查询，并且我找不到MySQL的明确文档来表明它不受支持。在MariaDB中，文档表明它不受支持，并且生成的表达式只能包括当前行内的数据。我只是想确保我没有漏掉什么。 - user1955162

Answer 1

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

生成列表达式必须遵循以下规则。如果表达式包含不允许的结构，则会发生错误。

• 不允许使用子查询、参数、变量、存储函数和用户定义函数。

对于生成列的表达式，只能引用同一行内的列是合理的。生成列不能使用子查询，或者引用其他表或具有非确定性输出的函数。

假设生成列支持跨表引用。特别考虑存储的生成列的情况。

如果您更新一个表，MySQL还必须更新数据库中其他地方生成列中的任何引用，如果它们引用了您更新的行。对于MySQL来说，跟踪所有这些引用是复杂且昂贵的。

然后考虑通过存储函数添加间接引用。

考虑到您的更新是针对事务中的InnoDB表，但生成的列可能在非事务（MyISAM，MEMORY，ARCHIVE等）表中。当您进行更新时，是否应该反映在那些生成的列中？如果您回滚会怎样？是否应该在提交时反映您的更新？那么MySQL应该如何“排队”以应用这些表的更改？如果多个事务提交影响生成列引用的更新，哪一个应该获胜，应用了最后一次更改的事务还是最后提交的事务？因此，允许生成的列引用同一表中同一行的列之外的任何内容是不实际或有效的。

- Bill Karwin

Answer 2

计算列的概念是从记录中的其他列推导数据，例如将国家代码与邮政编码组合在一起，因此您可以存储DE和12345并获得DE-12345，这可用于地址。

然而，您所尝试的是完全不同的事情。您正在访问另一个表中的数据。但是该表的数据可能会更改，因此当访问相同的记录时，您可能会突然得到完全不同的结果。计算列应包含确定性值，因此只要记录数据不更改，它们就不会更改。我不知道MySQL在这方面的情况，但它可能禁止非确定性数据，如您的子查询。

实际上，您正在寻找的是视图。视图可以组合来自不同表的选择，就像您想要发生的那样。所以使用：

create view familydata as
(
  select f.*, sum(c.age) as sumofages
  from families f
  join children c on c.family_id = f.id
  group by f.id
);

或者

create view familydata as
(
  select f.*,
  (
    select sum(age)
    from children c
    where c.family_id = f.id
  ) as sumofages
  from families f
);

我希望我把语法写对了。

- Thorsten Kettner