业务场景:
例如用户表,我们需要建一个字段是创建时间, 一个字段是更新时间.
解决办法可以是指定插入时间,也可以使用数据库的默认时间.
在mysql中如果设置两个默认CURRENT_TIMESTAMP,会出现这样的错误.
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.
错误的建表语句:
CREATE TABLETBL_FUND_FROZEN_UNFROZEN_RECORD
(
IDBIGINT NOT NULLAUTO_INCREMENT,
TRADE_FLOW_IDVARCHAR(60) NOT NULL,
ACCOUNT_NOVARCHAR(32),
INITIATORVARCHAR(16),
CREATE_DATETIMESTAMP default CURRENT_TIMESTAMP,
OPERATE_TYPEVARCHAR(32) NOT NULL,
FROZEN_AMOUNTDECIMAL(18,2) DEFAULT 0.0 NOT NULL,
UNFROZEN_AMOUNTDECIMAL(18,2) DEFAULT 0.0,
CREDENTIALVARCHAR(40) NOT NULL,
MODIFY_DATETIMESTAMP default CURRENT_TIMESTAMP,
UNFROZEN_DATETIMESTAMP,
SERIAL_NUMBIGINT NOT NULL,
VERSIONBIGINT,
DECRIPTIONVARCHAR(200),CONSTRAINT P_Key_1 PRIMARY KEY(ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
使用函数的方式解决该问题:
通过如下方式指定时间戳类型:
create tabletest_table(
idinteger not null auto_increment primary key,
stamp_createdtimestamp default '0000-00-00 00:00:00',
stamp_updatedtimestamp default now() on updatenow()
);
进行如下测试,验证功能是否生效:
mysql> insert into test_table(stamp_created, stamp_updated) values(null, null);
Query OK, 1 row affected (0.06 sec)
mysql> select * from t5;
+----+---------------------+---------------------+
| id | stamp_created | stamp_updated |
+----+---------------------+---------------------+
| 2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update test_table set id = 3 where id = 2;
Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created | stamp_updated |
+----+---------------------+---------------------+
| 3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)
文章出处: