子查询作为mysql中生成的列? [英] Subquery as generated column in mysql?

问题描述

我可以在表A中创建一个生成的列，以将表B中的一列与表A中的行的tableA_id相加吗?

Can I create a generated column in table A which sums up a column in table B with a tableA_id of the row in table A?

假设我有一个家庭表和一个孩子表.我想要每个家庭的孩子年龄的总和.

Suppose I have a table of of families, and a table of children. I want a sum of the ages of the children for each family.

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类型.我在这里做什么错了?

I can't save it as type VIRTUAL either. What am I doing wrong here?

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似乎并非如此.也许选择?

I don't know which function is disallowed. SUM doesn't seem to be it. Maybe SELECT?

推荐答案

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

生成的列表达式必须遵守以下规则.一个 如果表达式包含不允许的构造，则会发生错误.

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

• 子查询，参数，变量，存储的函数和用户定义的 不允许使用功能.

• Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.

生成的列的表达式只能引用同一行中的 列是合理的.生成的列不能使用子查询，也不能引用其他表或具有不确定输出的函数.

It's reasonable that the expression for a generated column can reference only columns within the same row. The generated column cannot use subqueries, or reference other tables, or functions with non-deterministic output.

假设生成的列确实支持跨表引用.特别考虑STORED生成的列的情况.

Suppose generated columns did support cross-table references. Particularly consider the case of STORED generated columns.

如果更新表，MySQL还必须更新数据库中其他位置生成的列中的所有引用，前提是它们引用了更新的行. MySQL跟踪所有这些引用将是复杂且昂贵的.

If you update a table, MySQL would also have to update any references in generated columns elsewhere in the database, if they reference the row you updated. It would be complex and expensive for MySQL to track down all those references.

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

Then consider add indirect references through stored functions.

然后考虑您的更新是在事务中的InnoDB表上进行的，但是生成的列可能在非事务(MyISAM，MEMORY，ARCHIVE等)表中.进行更新时，更新是否应反映在这些生成的列中?如果回滚怎么办?提交时是否应反映出您的更新?那么MySQL应该如何排队"更改以应用于这些表?如果多个事务提交影响生成的列引用的更新怎么办?哪一个应该获胜，最后应用更改的是谁或最后提交的更改?

Then consider that your update is to an InnoDB table in a transaction, but the generated column may be in a non-transaction (MyISAM, MEMORY, ARCHIVE, etc.) table. Should your update be reflected in those generated columns when you make it? What if you roll back? Should your update be reflected at the time you commit? Then how should MySQL "queue up" changes to apply to those tables? What if multiple transactions commit updates that affect the generated column reference? Which one should win, the one that applied the change last or the one that committed last?

由于这些原因，允许生成的列引用同一表中同一行的列以外的任何内容都是不切实际或无效的.

For these reasons, it's not practical or efficient to allow generated columns to reference anything other than the columns of the same row in the same table.

这篇关于子查询作为mysql中生成的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！