MySQL CLAUSE can become a value?

Question

I am new in mysql. What I would to do is create a new table which is a copy of the original one table with one more column under a specific condition. Which condition appears as a new column is the new table. I mean:

Let table be a sequence of given point (x,y) I want to create the table temp being (x,y,r) where r = x^2 + y^2<1 But what I did is

CREATE temp LIKE table;
ALTER TABLE temp ADD r FLOAT;

INSERT INTO temp (x,y) SELECT * FROM table WHERE x*x+y*y<1;
UPDATE temp SET r=x*x+y*y;

It is ok, it gives what I want, but my database is much more bigger than this simple example and here I calculate twice the radius r in two table. It is not so good about optimization.

Is there a way to pass the clause into the new column directly?

Thanks in advance.

Answer 1

You should (almost) never store calculated data in a database. It ends up creating maintenance and application nightmares when the calculated values end up out of sync with the values from which they are calculated.

At this point you're probably saying to yourself, "Well, I'll do a really good job keeping them in sync." It doesn't matter, because down the road at some point, for whatever reason, they will get out of sync.

Luckily, SQL provides a nice mechanism to handle what you want - views.

CREATE VIEW temp
AS
    SELECT
        x,
        y,
        x*x + y*y AS r
    FROM My_Table
    WHERE
        x*x + y*y < 1