如何根据公式填充mysql列值? [英] how to populate mysql column value based on a formula?
问题描述
我用以下几列创建了一个mysql表...
I have created a mysql table with the following columns...
item_price, discount, delivery_charge, grand_total
item_price
,discount
和delivery_charge
的值都根据所考虑的项目而有所不同,这些将手动填写到表中.
The value of item_price
, discount
, and delivery_charge
all vary depending on the item under consideration, and these would be filled into the table manually.
我想让mysql使用以下公式根据item_price
,discount
和delivery_charge
的值填充grand_total
列中的值.
I would like to have mysql populate the value in the grand_total
column based on the values of item_price
, discount
, and delivery_charge
, using the formula below...
grand_total = item_price - discount + delivery_charge
我可以在mysql中创建表结构时以某种方式为此列指定公式吗?我知道可以使用php完成此操作,但我更喜欢数据库(如果可能)自动为我完成操作.
Can I somehow specify a formula for this column while creating the table structure in mysql? I know this can be done using php, but I prefer the database doing this for me automatically, if possible.
推荐答案
以下是将起作用的触发器的示例.请注意,由于临时更新"的要求,您既需要更新触发器,又需要插入触发器.
Here's an example of a trigger that will work. Note that you'll need both update and insert triggers due to your "ad hoc update" requirements.
delimiter ~
create trigger my_table_update before update on my_table
for each row begin
set new.grand_total = new.item_price - new.discount + new.delivery_charge;
end~
create trigger my_table_insert before insert on my_table
for each row begin
set new.grand_total = new.item_price - new.discount + new.delivery_charge;
end~
delimiter ;
但是视图会不会变得更简单更好?
But wouldn't a view be simpler and better?
create view my_table_view as
select item_price, discount, delivery_charge,
item_price - discount + delivery_charge as grand_total
from my_table;
然后仅从视图中选择而不是从表中选择
then just select from the view instead of the table
这篇关于如何根据公式填充mysql列值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!