MySQL 从另一列计算的列？

Column calculated from another column?

提问by Matthew

Given the following table:

鉴于下表：

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

Is there a way to add a column that is automatically calculated based on another column in the same table? Like a VIEW, but part of the same table. As an example, calculatedwould be half of value. Calculatedshould be automatically updated when valuechanges, just like a VIEW would be.

有没有办法添加根据同一个表中的另一列自动计算的列？类似于 VIEW，但属于同一个表的一部分。例如，calculated将是value. Calculated应该在value更改时自动更新，就像 VIEW 一样。

The result would be:

结果将是：

id | value | calculated
-----------------------
1     6       3
2     70      35

回答by Abhishek Gupta

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

Generated Column 是 MySql 5.7.6 及以上版本的好方法之一。

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.

这两种类型都可以有 NOT NULL 限制，但只有存储的 Generated Column 可以是索引的一部分。

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.

金额会自动弹出表格，您可以直接访问它，另外请注意，每当您更新任何列时，金额也会更新。

回答by Vincent Mimoun-Prat

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 查询以计算新列的值，如下所示：

UPDATE mytable SET calculated = value/2;

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

如果它必须是自动的，并且您的 MySQL 版本允许，您可以尝试使用触发器

回答by Jonathan Allen

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.

MySQL 5.7 支持计算列。他们称之为“生成列”，语法有点奇怪，但它支持我在其他数据库中看到的相同选项。

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

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

回答by Jerry

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

@krtek 的答案是正确的，但有几个问题。

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.

坏消息是在同一个表的触发器中使用 UPDATE 将不起作用。好消息是这不是必需的。有一个新对象，您甚至可以在触摸桌子之前对其进行操作。

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:

还要注意 BEGIN...END; 语法必须使用不同的分隔符进行解析。整个shebang变成：

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 ;

回答by krtek

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.

我不认为你只能触发一个，因为我们必须响应的事件是不同的。

回答by PodTech.io

You can use generated columns from MYSQL 5.7.

您可以使用 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

• 虚拟：从表中读取记录时动态计算（默认）
• 存储：在表中插入/更新新记录时计算

回答by Doron

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

希望这可以帮助...