MySQL: Computed Column

Question

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

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;

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).

Have you tried to use triggers? Check this post out : http://stackoverflow.com/questions/5222044/column-calculated-from-another-column. Hope it helps :) — Victor, Sep 17 '13 at 20:23

score 12 · Answer 1 · answered Mar 24 '11 at 17:30

A computed column normally means a value you can calculate per row. MySQL does not support that, but SQL Server does. For example, to store the sum of two columns permanently:

create table Table1 (a int, b int, c as a+b persisted)

However, you're looking to store an aggregate, that is, a value for a group of rows. MySQL and SQL Server don't support materialized views with an aggregate, but Oracle does:

create table Table1 (a int, b int);

create materialized view View1 as
select  a
,       count(*) as Cnt
from    Table1
group by
    a;

With MySQL however, the closest you can do is a cronjob that periodically populates a table:

truncate table Table1Summary;
insert Table1Summary (a, Cnt) select a, count(*) from Table1;

You can query the table like a materialized view; it will be as fast, but not guaranteed to be up to date.

Thanks for the help, I guess sticking with a query makes more sense then since it's always up to date. — Matt, Mar 24 '11 at 17:57
MySQL 5.7 supports generated columns, where an expression which can be stored or virtual, and a virtual generated column can be indexed where the index stores the value. https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html — karmakaze, Dec 10 '20 at 14:57

score 4 · Accepted Answer · answered Mar 24 '11 at 17:03

it is possible. but you should not do it.

that is called denormalization - and is usually not a good idea at all.

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.

score 2 · Answer 3 · answered Dec 15 '17 at 01:17

2

MySql does support generated columns in 5.7

answered Dec 15 '17 at 01:17

Jim Sosa

578
3
21

Relevant link to the docs: https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html – houcros Oct 16 '19 at 08:32
I am trying to use the auto-generated columns functionality via MySQL while trying to interact with the db using JDBC. -The auto-generated cell which should actually display the result as per the framed query, that cell is editable and not blocked for auto-generation, and erroring out. Let me know please what changes do i need to my code for that row to auto populate. Also, since auto-generation feature got introduced in Mysql 5.7, but I am using 5.1 ver connector/driver. Could that be a problem?? – Ashish Ramtri Nov 03 '20 at 10:32

score 2 · Answer 4 · answered Mar 24 '11 at 17:24

2

In mysql you if you want an actual column you would

create it as any column
make sure it is consistent by creating a trigger on theatre table that will update the column ever time a row is added or deleted

answered Mar 24 '11 at 17:24

Unreason

12,556
2
34
50

MySQL: Computed Column

4 Answers4

Linked