Calculated columns in mysql on INSERT statements

Question

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?

e.g. do something like:

INSERT INTO log (date='foo');

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.

Answer 1

You definitly have to declare what to insert. This should be possible by using the INSERT ... SELECT statement.

INSERT INTO log (date, count)
    SELECT DATE() as date, count(id) as count
    from foo;

Which should insert a new row into the log table, containing todays date and the number of rows in the foo table. (Assuming the foo table has an id column.. Use the primary key or another indexed column)

Answer 2

Or use CREATE TRIGGER.

Answer 3

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;