I'm trying to do this, but it seems like MySQL isn't allowing me. Is there a solution to this issue or am I expected to always include the function in my INSERT queries?
CREATE TABLE foo(
created INT NOT NULL DEFAULT UNIX_TIMESTAMP()
)
I'm aware of the TIMESTAMP type that accepts a CURRENT_TIMESTAMP default, but my client insisted on using epoch time in the database.
The way MySQL implements the TIMESTAMP
data type, it is actually storing the epoch time in the database. So you could just use a TIMESTAMP
column with a default of CURRENT_TIMESTAMP
and apply the UNIX_TIMESTAMP()
to it if you want to display it as an int:
CREATE TABLE foo(
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
insert into foo values (current_Date()),(now());
select unix_timestamp(created) from foo;
+-------------------------+
| unix_timestamp(created) |
+-------------------------+
| 1300248000 |
| 1300306959 |
+-------------------------+
2 rows in set (0.00 sec)
However, if you really want the datatype of the column to be INT
, you can use R. Bemrose's suggestion and set it via trigger:
CREATE TABLE foo(
created INT NULL
);
delimiter $$
create trigger tr_b_ins_foo before insert on foo for each row
begin
if (new.created is null)
then
set new.created = unix_timestamp();
end if;
end $$
delimiter ;
insert into foo values (unix_timestamp(current_Date())), (null);
select created from foo;
+------------+
| created |
+------------+
| 1300248000 |
| 1300306995 |
+------------+
2 rows in set (0.00 sec)
From the documentation:
With one exception, the default value
must be a constant; it cannot be a
function or an expression. This means,
for example, that you cannot set the
default for a date column to be the
value of a function such as NOW() or
CURRENT_DATE. The exception is that
you can specify CURRENT_TIMESTAMP as
the default for a TIMESTAMP column.
You can create triggers for this.
for insertion
CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
CREATE TRIGGER my_trigger_name_1 BEFORE INSERT ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
for update
CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
CREATE TRIGGER my_trigger_name_2 BEFORE UPDATE ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
Note : I have no idea about the performance of MYSQL TRIGGER
Please go through these links
Identify some of the drawback of implementing sql server triggers
Using Triggers
Well, if MySQL won't let you do it directly, you can always do it using a BEFORE INSERT... FOR EACH ROW
trigger.