从另一列计算的列？

如下表所示:

id | value
--------------
1     6
2     70

是否有方法添加一个根据同一表中的另一列自动计算的列？就像一个视图，但是是同一张桌子的一部分。例如，calculated是 value的一半。当 value改变时，Calculated应该自动更新，就像视图一样。

结果将是:

id | value | calculated
-----------------------
1     6       3
2     70      35
230335 次浏览
小开

If it is a selection, you can do it as:

SELECT id, value, (value/2) AS calculated FROM mytable

Else, you can also first alter the table to add the missing column and then do an UPDATE query to compute the values for the new column as:

UPDATE mytable SET calculated = value/2;

If it must be automatic, and your MySQL version allows it, you can try with triggers

小开

If you want to add a column to your table which is automatically updated to half of some other column, you can do that with a trigger.

But I think the already proposed answer are a better way to do this.

Dry coded trigger :

CREATE TRIGGER halfcolumn_insert AFTER INSERT ON table
FOR EACH ROW BEGIN
UPDATE table SET calculated = value / 2 WHERE id = NEW.id;
END;
CREATE TRIGGER halfcolumn_update AFTER UPDATE ON table
FOR EACH ROW BEGIN
UPDATE table SET calculated = value / 2 WHERE id = NEW.id;
END;

I don't think you can make only one trigger, since the event we must respond to are different.

小开

@krtek's answer is in the right direction, but has a couple of issues.

The bad news is that using UPDATE in a trigger on the same table won't work. The good news is that it's not necessary; there is a NEW object that you can operate on before the table is even touched.

The trigger becomes:

CREATE TRIGGER halfcolumn_update BEFORE UPDATE ON my_table
FOR EACH ROW BEGIN
SET NEW.calculated = NEW.value/2;
END;

Note also that the BEGIN...END; syntax has to be parsed with a different delimiter in effect. The whole shebang becomes:

DELIMITER |


CREATE TRIGGER halfcolumn_insert BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
SET NEW.calculated = NEW.value/2;
END;
|


CREATE TRIGGER halfcolumn_update BEFORE UPDATE ON my_table
FOR EACH ROW BEGIN
SET NEW.calculated = NEW.value/2;
END;
|


DELIMITER ;
小开

MySQL 5.7 supports computed columns. They call it "Generated Columns" and the syntax is a little weird, but it supports the same options I see in other databases.

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

小开

Generated Column is one of the good approach for MySql version which is 5.7.6 and above.

There are two kinds of Generated Columns:

  • Virtual (default) - column will be calculated on the fly when a record is read from a table
  • Stored - column will be calculated when a new record is written/updated in the table

Both types can have NOT NULL restrictions, but only a stored Generated Column can be a part of an index.

For current case, we are going to use stored generated column. To implement I have considered that both of the values required for calculation are present in table

CREATE TABLE order_details (price DOUBLE, quantity INT, amount DOUBLE AS (price * quantity));


INSERT INTO order_details (price, quantity) VALUES(100,1),(300,4),(60,8);

amount will automatically pop up in table and you can access it directly, also please note that whenever you will update any of the columns, amount will also get updated.

小开

I hope this still helps someone as many people might get to this article. If you need a computed column, why not just expose your desired columns in a view ? Don't just save data or overload the performance with triggers... simply expose the data you need already formatted/calculated in a view.

Hope this helps...

小开

You can use generated columns from MYSQL 5.7.

Example Usage:

ALTER TABLE tbl_test
ADD COLUMN calc_val INT
GENERATED ALWAYS AS (((`column1` - 1) * 16) + `column2`) STORED;

VIRTUAL / STORED

  • Virtual: calculated on the fly when a record is read from a table (default)
  • Stored: calculated when a new record is inserted/updated within the table

提交答案