从另一列计算的列？

Question

给定以下表格：

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

有没有一种方法可以添加一列，该列根据同一表中的另一列自动计算？就像视图一样，但是属于同一个表。例如，calculated应该是value的一半。当value更改时，calculated应该被自动更新，就像视图一样。

结果应该是：

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

- Matthew

---

为什么不使用视图（VIEW）？ - Tim Child

持久化（又称存储）的计算列通常更便宜以阅读，因为它们像其他列一样被存储。它们甚至可以被索引。 - Jonathan Allen

非持久化计算列只是一种方便的功能。在处理ORM时，它通常比视图更有效。 - Jonathan Allen

Answer 1

生成列是MySQL 5.7.6及以上版本的一个好方法之一。

生成列有两种类型：

• 虚拟（默认）- 当从表中读取记录时，将即时计算该列
• 存储 - 当写入/更新表中的新纪录时，将计算该列

这两种类型都可以有NOT NULL限制，但只有存储生成的列才能成为索引的一部分。

对于当前情况，我们将使用存储的生成列。要实现这一点，我考虑到所需的两个计算值都存在于表中。

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

金额将自动弹出在表格中，您可以直接访问它。同时请注意，每当您更新任何列时，金额也会得到更新。

- Abhishek Gupta

---

根据文档，这仅适用于NDB存储引擎，而不是InnoDB。 - cliffordheath

@cliffordheath - 你错了。我使用mySQL 5.7、InnoDB引擎和生成列创建了一个示例sql fiddle，它可以正常工作：db-fiddle。你所提到的mySQL doc确实有误导性，但应该理解为：“从mySQL 5.7开始支持生成列，并且从MySQL NDB Cluster 7.5.3开始支持NDB存储引擎”。 - user2988142

@user2988142 很好知道。即使您的措辞可能存在歧义。也许可以提交文档错误报告？ - cliffordheath

我正在尝试使用JDBC与MySQL交互时，通过MySQL自动生成列功能。应该实际显示受框架查询影响的自动生成单元格是可编辑的，而不是被阻止自动生成，并且出现错误。请告诉我需要更改代码以使该行自动填充。另外，由于自动生成功能是在Mysql 5.7中引入的，但我正在使用5.1版本的连接器/驱动程序。这可能是个问题吗？ - Ashish Ramtri

AWS Aurora MySQL实例在使用派生列作为复合索引的一部分时崩溃。mysql # 23533396 - veritas

Answer 2

如果是一个选择，你可以这样做：

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

否则，您也可以先更改表以添加缺少的列，然后执行UPDATE查询以计算新列的值：

UPDATE mytable SET calculated = value/2;

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

- Vincent Mimoun-Prat

---

没错，这是一个SELECT语句，而不是向表中添加列。 - Matthew

你能否使用那个“calculated”列在同一查询中进行进一步的计算？比如(calculated*2) AS double_calculated？ - ZurabWeb

@ZurabWeb，你可以使用嵌套查询（outer select 当中可用）。 - Kamil Gosciminski

@ZurabWeb 是的，你可以：SELECT n_rows, (n_rows / 2) AS calculated, (SELECT calculated) * 2 AS double_calculated FROM mysql.innodb_table_stats - wiktor

Answer 3

MySQL 5.7支持计算列的功能。它们称其为“生成列”，语法有些奇怪，但是它支持其他数据库中的相同选项。

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

- Jonathan Allen

---

不幸的是，“生成列”不是ISO SQL标准的一部分。看起来唯一的“ISO SQL”解决方案是使用视图。 - PowerGamer

Answer 4

@krtek的回答是正确的方向，但存在一些问题。

坏消息是在触发器中使用UPDATE来更新同一个表是行不通的。好消息是这并非必须；有一个NEW对象可以在表被操作之前进行操作。

触发器变成了：

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

注意BEGIN...END;语法需要使用不同的定界符进行解析。整个代码块变为：

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 ;

- Jerry

---

请在上面的代码中替换“table”这个单词为你的表名。我认为作者只是忘记了表名，在“ON table”后添加了它，并因此浪费了几分钟的时间。 - Bloodboiler

好的点子 @Bloodboiler - 我修改了代码片段，使其更加清晰易懂。 - Jerry

请注意，多年以后，MySQL已经提出了一种更加优雅的方法来完成这个任务。这个答案仍然有效，但是有一些很好的理由不使用触发器。如果Abhishek Gupta的答案对您来说是实用的，那么您将得到一个更易于维护的系统。 - Jerry

Answer 5

您可以使用来自MYSQL 5.7的生成列。

示例用法：

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

虚拟 / 存储

• 虚拟：当从表中读取记录时，根据需要计算（默认）
• 存储：在插入/更新新纪录时，对其进行计算

- PodTech.io

Answer 6

如果您想向表中添加一个列，并自动更新为另一列的一半，您可以使用触发器来实现。但我认为已经提出的答案是更好的方法。以下是干代码触发器：

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;

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

- krtek

---

谢谢。在这种情况下，我们也应该考虑删除吗？ - Ernest Han

Answer 7

我希望这篇文章可以帮助到更多的人。如果你需要一个计算列，为什么不在视图中公开你需要的列呢？不要只是保存数据或使用触发器过载性能... 在视图中公开你需要的数据已经格式化/计算好。

希望这可以帮助到你...

- Doron

---

这要看情况，如果OLAP解决方案过于繁琐，您可能希望添加带有舍入值的列，以用于快速报告目的（因此分组聚合始终总和相同金额作为CRM / ERP或银行账户）。在需要进行超过微不足道的计算的情况下，存储这些值可以加快报告速度，例如销售利润率计算，或者需要连接其他表来进行计算。最好在插入数据时插入值，而不是在需要报告时将所有内容混在一起。 - Louis Somers