問(wèn)題描述
給定下表:
id | value
--------------
1 6
2 70
有沒(méi)有辦法添加一個(gè)基于同一個(gè)表中的另一列自動(dòng)計(jì)算的列?類似于 VIEW，但屬于同一個(gè)表的一部分.例如，calculated 將是 value 的一半.Calculated 應(yīng)該在 value 更改時(shí)自動(dòng)更新，就像 VIEW 一樣.
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, calculated would be half of value. Calculated should be automatically updated when value changes, just like a VIEW would be.
結(jié)果是:
id | value | calculated
-----------------------
1 6 3
2 70 35
推薦答案
Generated Column 是 MySql 5.7.6 及以上版本的好方法之一.
Generated Column is one of the good approach for MySql version which is 5.7.6 and above.
生成的列有兩種:
- 虛擬(默認(rèn)) - 列將在運(yùn)行時(shí)計(jì)算從表中讀取記錄
- Stored - 列將在在表中寫入/更新新記錄
兩種類型都可以有 NOT NULL 限制，但只有存儲(chǔ)的 Generated Column 可以是索引的一部分.
Both types can have NOT NULL restrictions, but only a stored Generated Column can be a part of an index.
對(duì)于當(dāng)前情況，我們將使用存儲(chǔ)的生成列.為了實(shí)現(xiàn)，我認(rèn)為計(jì)算所需的兩個(gè)值都存在于表中
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 會(huì)自動(dòng)彈出到表格中，您可以直接訪問(wèn)它，另外請(qǐng)注意，每當(dāng)您更新任何列時(shí)，amount 也會(huì)更新.
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.
這篇關(guān)于從另一列計(jì)算的列?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！