mysql上INSERT语句中的计算列 [英] Calculated columns in mysql on INSERT statements

问题描述

比方说，我想要一个表来记录其他表中的日期和列数(或者实际上是任何数学/字符串连接等).

Let's say that I want to have a table that logs the date and the number of columns in some other table (or really any sort of math / string concat etc).

CREATE TABLE `log` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
`date` DATETIME NOT NULL ,
`count` INTEGER NOT NULL ,
PRIMARY KEY (`id`)
);

每次插入时是否可以为我计算计数列?

Is it possible to have the count column calculated for me whenever I do an insert?

例如做类似的事情:

INSERT INTO log (date='foo');

并具有由mysql计算的计数.

and have count calculated by mysql.

很明显，我可以自己做一个查询来获取计数并插入计数，但这会更好.

Obviously I could do it myself by doing a query to get the count and inserting it, but this would be better.

推荐答案

当通过插入，更新或删除来更改表时，触发器是用于注释数据的最佳工具.

Triggers are the best tool for annotating data when a table is changed by insert, update or delete.

要使用当前日期自动将日志中新行的日期列设置为当前日期，您将创建一个类似于以下内容的触发器:

To automatically set the date column of a new row in the log with the current date, you'd create a trigger that looked something like this:

create trigger log_date before insert on log 
for each row begin
   set new.date = current_date()
end;

这篇关于mysql上INSERT语句中的计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！