MySQL MySQL计算派生属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14526140/
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
MySQL Calculate Derived Attribute
提问by user1628340
I have a derived attribute Finein a MySQL table, whose value is
我Fine在 MySQL 表中有一个派生属性，其值为
(CurrentDate - DueDate) * 0.5
CurrentDateand DueDateare stored in the table in the Dateformat.
CurrentDate并DueDate以Date格式存储在表中。
How can I specify this formula for Fineattribute in my table
如何为Fine表中的属性指定此公式
CREATE TABLE IF NOT EXISTS Loan (
loanID INT UNSIGNED NOT NULL AUTO_INCREMENT,
BorrowDate DATE DEFAULT NULL,
ReturnDate DATE DEFAULT NULL,
DueDate DATE DEFAULT NULL,
--- Fine... What do I write for Fine here-------
userID INT UNSIGNED NOT NULL,
itemID INT UNSIGNED NOT NULL,
PRIMARY KEY (loanID) ,
FOREIGN KEY (userID) REFERENCES LibraryUser (userID),
FOREIGN KEY (itemID) REFERENCES Items (itemID)
);
回答by Rapha?l Althaus
You can't have a computed column "as is" in mysql.
您不能在 mysql 中“按原样”拥有计算列。
Three solutions :
三种解决方案：
First: use triggers
第一：使用触发器
see for example column calculated from another column
参见例如从另一列计算的列
Second: create a view (Fine will exist only in your view, not in your main table).
第二：创建一个视图（Fine 将仅存在于您的视图中，而不存在于您的主表中）。
view can contain computed columns without any problem.
视图可以包含计算列，没有任何问题。
CREATE VIEW v_computedLoan AS
SELECT
loanID,
BorrowDate,
ReturnDate,
CurrentDate,
--etc
(CurrentDate-DueDate)*0.5 as Fine
FROM Loan
Third: keep calculation out of your db. Use this in your queries only.
第三：将计算排除在您的数据库之外。仅在您的查询中使用它。
EDIT : if CurrentDate is really the same as CURRENT_DATE() function (and then should vary), solution with triggers won't work, and CurrentDate shouldn't be stored in any table (but you can still use it in a view).
编辑：如果 CurrentDate 确实与 CURRENT_DATE() 函数相同（然后应该有所不同），则使用触发器的解决方案将不起作用，并且不应将 CurrentDate 存储在任何表中（但您仍然可以在视图中使用它）。
