2013-04-10 回答
mysql 中,默认值无法使用函数
也就是你无法 设置某一列,默认值是 now () 这样的处理
假如需要 某列的默认值为 当前数据库时间,那么可以使用 timestamp 数据类型。插入的时候,忽略该列 即可。
dt timestamp
等价于
dt timestamp default current_timestamp on update current_timestamp
create table testb (
id int primary key,
val varchar(10),
dt timestamp
);
insert into testb(id, val) values(1, 'a');
insert into testb(id, val) values(2, 'b');
select
*
from
testb;
+----+------+---------------------+
| id | val | dt |
+----+------+---------------------+
| 1 | a | 2013-03-21 14:24:20 |
| 2 | b | 2013-03-21 14:24:21 |
+----+------+---------------------+
2 rows in set (0.00 sec)
update
testb
set
val = 'c'
where
id = 1;
select
*
from
testb;
+----+------+---------------------+
| id | val | dt |
+----+------+---------------------+
| 1 | c | 2013-03-21 14:28:02 |
| 2 | b | 2013-03-21 14:24:21 |
+----+------+---------------------+
2 rows in set (0.00 sec)
--------------------------------------------------------------------------------
假如仅仅需要 插入时记录时间, 更新的时候不需要, 那么需要修改表定义方式:
timestamp not null default current_timestamp
create table testc (
id int primary key,
val varchar(10),
dt timestamp not null default current_timestamp
);
insert into testc(id, val) values(1, 'a');
insert into testc(id, val) values(2, 'b');
select
*
from
testc;
+----+------+---------------------+
| id | val | dt |
+----+------+---------------------+
| 1 | a | 2013-03-21 14:35:12 |
| 2 | b | 2013-03-21 14:35:13 |
+----+------+---------------------+
2 rows in set (0.00 sec)
update
testc
set
val = 'cc'
where
id = 1;
select
*
from
testc;
+----+------+---------------------+
| id | val | dt |
+----+------+---------------------+
| 1 | cc | 2013-03-21 14:35:12 |
| 2 | b | 2013-03-21 14:35:13 |
+----+------+---------------------+
2 rows in set (0.00 sec)