MySQL里如何自动更新时间字段

-- 比如datetime字段为：sjupdate 表名 set sj = now() where 条件//now()是当前本地年月日时分秒update 表名 set sj = '2014-10-10 12:00:00' where 条件

既然楼主已经解决了， 我就顺便粘贴点 测试的 sql 代码上来吧

首先是 插入 与 更新， 都会修改 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)

---