mysql default unix_timestamp,是否可以创建一个在MySQL中使用UNIX_TIMESTAMP默认值的列？...

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)