Often it is quite reasonable to have the database to take care of some audit related columns like date added or updated.
Unfortunately MySQL does NOT allow functions within table definitions like let’s say MS SQL does, e.g.:
CREATE TABLE t (f datetime NOT NULL DEFAULT now()); |
create table t (f datetime not null default now());
The only work around is to use timestamp column instead of date or datetime ones. There can be only one auto set timestamp column though.
So I normally use it to store date updated and save the date created manually.
CREATE TABLE t
(
name VARCHAR(20),
date_updated TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) |
create table t
(
name varchar(20),
date_updated timestamp not null
default current_timestamp on update current_timestamp
)
Also there are two things to keep in mind:
- date_updated will be set based on MySQL server time. This is especially important if other data is stored using GMT or non-server time zone.
- date_updated must NOT be a part of update or insert query.
-- set date_updated to current time
INSERT INTO t (name) VALUES ('test');
UPDATE t SET name = 'test';
-- override date_updated to certain value
INSERT INTO t (name, date_updated)
VALUES ('test', '2011-08-02 20:00:00');
UPDATE t SET name = 'test',
date_updated = '2011-08-02 20:00:00'; |
-- set date_updated to current time
insert into t (name) values ('test');
update t set name = 'test';
-- override date_updated to certain value
insert into t (name, date_updated)
values ('test', '2011-08-02 20:00:00');
update t set name = 'test',
date_updated = '2011-08-02 20:00:00';
Alternatively it is possible to use triggers to have as many auto updated datetime columns as needed. However personally I prefer to avoid triggers as much as possible. Mostly because often triggers are misused and as result application complexity is increased for no reason.
Comments(0) 