如何使用公式和来自同一行其他列的数据自动更新 MYSQL 列值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8076971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to automatically update a MYSQL column value using formula and data from other columns in same row?
提问by arun nair
Question on mySQL.
关于mySQL的问题。
I have a table with columns like below:
我有一个包含如下列的表格：
BasePrice, Discount, NetPrice
Now assume that the calculation of net price happens like below:
现在假设净价的计算如下：
NetPrice = BasePrice - Discount
Now, I do not mind entering the BasePriceand Discountvalues into the column on my own, manually using phpMyadmin (that is, direct backend updation). Since the NetPricevalue involves calculation as shown above, I do not want to update the column value for each row in the table, but would prefer the database updating it automatically for me ( not that I am weak at math :)).
现在，我不介意使用 phpMyadmin 手动将BasePrice和Discount值输入到列中（即直接后端更新）。由于该NetPrice值涉及如上所示的计算，我不想更新表中每一行的列值，但更希望数据库自动为我更新它 ( not that I am weak at math :))。
Is there someway to have the database update the NetPricecolumn value automatically? I know I can use php to get the other two column values, calculate and then update the NetPricevalue, again using php code, but would prefer the db doing it on its own without any php/server side scripting.
有没有办法让数据库NetPrice自动更新列值？我知道我可以使用 php 来获取其他两列值，计算然后更新NetPrice值，再次使用 php 代码，但更喜欢 db 在没有任何 php/服务器端脚本的情况下自行完成。
I use mySQL and phpMyadmin for dev.
我使用 mySQL 和 phpMyadmin 进行开发。
Thanks.
谢谢。
回答by Marco
You could use a single query to update all table:
您可以使用单个查询来更新所有表：
UPDATE your_table
SET NetPrice = BasePrice - Discount
or you can use a trigger to alter that column anytime a row is added.
Something like this:
或者您可以使用触发器在添加行时随时更改该列。
像这样的东西：
CREATE TRIGGER onInsert BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN
SET NEW.NetPrice = NEW.BasePrice - NEW.Discount;
END;
回答by spencer7593
As an alternative to Marco's (+1) answer, for just an INSERT, the simplest approach might be to reference columns assigned previously in the statement. For example:
作为 Marco (+1) 答案的替代方案，对于 INSERT，最简单的方法可能是引用先前在语句中分配的列。例如：
INSERT INTO t (BasePrice, Discount, NetPrice) VALUES (17.00, 2.50, BasePrice-Discount);
For that NetPrice column to calculated correctly, the BasePriceand Discountcolumns must appear in the insert beforethe expression that calculates NetPrice. (The values assigned to each column is available later in the statement, afterthe column is assigned a value.)
为了正确计算 NetPrice 列，BasePrice和Discount列必须出现在计算 的表达式之前的插入中NetPrice。（分配给每一列的值在语句的后面，在为列分配一个值之后可用。）
If you always want to guarantee that NetPrice is kept "in sync" if either BasePrice or Discount is modified, then a BEFORE UPDATEtrigger will do that for you, so the combination of a BEFORE INSERTand BEFORE UPDATEtrigger would be appropriate. (See the answer from Marco.)
如果您总是希望在 BasePrice 或 Discount 被修改时保证 NetPrice 保持“同步”，那么BEFORE UPDATE触发器将为您做到这一点，因此 aBEFORE INSERT和BEFORE UPDATE触发器的组合将是合适的。（请参阅 Marco 的回答。）
