MySQL：计算列 [英] MySQL: Computed Column

问题描述

我刚刚开始使用SQL并遇到问题。

I'm just started using SQL and ran into a problem.

在我的数据库中，我目前有两个表，电影院和剧院。
我试图在Cinemas表中创建一个列＃of Theaters＃，用于计算剧院表中具有与Cinemas表中的CinemaID相同的CinemaID（外键）的剧院数量，我得到了作为SQL查询：

In my database, I presently have two tables, Cinemas and Theatres. I'm trying to create a column "# of Theatres# in the Cinemas table that counts the number of Theatres in the Theatres table with the same CinemaID (foreign key) as a Cinema in the Cinemas table. I got it to work as an SQL query:

SELECT cinemas.CinemaID,Town,COUNT(*) AS '# of Theatres'
FROM cinemax.cinemas,cinemax.theatres
WHERE cinemas.CinemaID=theatres.CinemaID
GROUP BY cinemas.CinemaID;

但是想知道是否可以在Cinemas表中创建一个列，它会自动执行上面的查询并将值插入每一行。

But wanted to know if it's possible to create a column in the Cinemas table, that automatically performs the above query and inserts the value into each row.

这将是一个非常小的数据库，所以速度不是一个问题，我只是想学习如何做一个这样的计算列（如果可能的话）。

It is going to be a very small database so speed isn't really an issue, I just want to learn how to make such a computed column (if even possible).

推荐答案

这是可能的，但你不应该这样做。

it is possible. but you should not do it.

通常不是一个好主意。

有时候，你可能需要反正规化某些奇怪的原因（你的例子是无处接近一个很好的理由）。在这些情况下，您需要添加触发器代码以在系统中任何会影响结果的任何更改时自动管理值。

sometimes however, you might absolutely need to denormalize something for some odd reason (your example is nowhere near a good reason). in these cases, you need to add trigger code to automatically manage the values whenever anything changes in the system that would affect the results.

这篇关于MySQL：计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！