mysql 手动写时间_mysql中如何设置默认时间为当前时间

2016-11-25 回答

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)