------------------------------------------------------------------------------------------ --MSsql ------------------------------------------------------------------------------------------ -- drop table testtab create table testtab(id int,name varchar(10)); go insert into testtab(id,name)values(1,'aa'),(2,'bb'); go select * from testtab; go --方法一 alter table testtab add addtime datetime default(getdate()) --字段历史数据为null go --方法二 alter table testtab add addtime2 datetime not null default(getdate()) --字段历史数据自动填充 go --方法三 alter table testtab add addtime3 datetime; alter table testtab add constraint df_testtab_addtime3 default(getdate()) for addtime3; --字段历史数据为null go /* id name addtime addtime2 addtime3 -- ---- ------- ----------------------- -------- 1 aa NULL 2017-02-14 11:32:45.960 NULL 2 bb NULL 2017-02-14 11:32:45.960 NULL */ insert into testtab(id,name)values(3,'cc'),(4,'dd'); go select * from testtab; go /* id name addtime addtime2 addtime3 -- ---- ---------------------- ----------------------- -------------------- 1 aa NULL 2017-02-14 11:32:45.960 NULL 2 bb NULL 2017-02-14 11:32:45.960 NULL 3 cc 2017-02-14 11:33:57.193 2017-02-14 11:33:57.193 2017-02-14 11:33:57.193 4 dd 2017-02-14 11:33:57.193 2017-02-14 11:33:57.193 2017-02-14 11:33:57.193 */
------------------------------------------------------------------------------------------ --MysqL ------------------------------------------------------------------------------------------ -- drop table testtab; create table testtab(id int,name varchar(10)); insert into testtab(id,'bb'); select * from testtab; --方法一 alter table testtab add addtime datetime default current_timestamp; --字段历史数据自动填充 --方法二 alter table testtab add addtime2 datetime; alter table testtab modify column addtime2 datetime default current_timestamp; --字段历史数据为null --另 alter table testtab add addtime3 datetime not null; --字段历史数据为 "0000-00-00 00:00:00" +----+------+---------------------+----------+---------------------+ | id | name | addtime | addtime2 | addtime3 | +----+------+---------------------+----------+---------------------+ | 1 | aa | 2017-02-14 11:37:37 | NULL | 0000-00-00 00:00:00 | | 2 | bb | 2017-02-14 11:37:37 | NULL | 0000-00-00 00:00:00 | +----+------+---------------------+----------+---------------------+ insert into testtab(id,name,addtime3)values(3,'cc',now()),'dd',now()); select * from testtab; +----+------+---------------------+---------------------+---------------------+ | id | name | addtime | addtime2 | addtime3 | +----+------+---------------------+---------------------+---------------------+ | 1 | aa | 2017-02-14 11:37:37 | NULL | 0000-00-00 00:00:00 | | 2 | bb | 2017-02-14 11:37:37 | NULL | 0000-00-00 00:00:00 | | 3 | cc | 2017-02-14 11:38:58 | 2017-02-14 11:38:58 | 2017-02-14 11:38:58 | | 4 | dd | 2017-02-14 11:38:58 | 2017-02-14 11:38:58 | 2017-02-14 11:38:58 | +----+------+---------------------+---------------------+---------------------+
------------------------------------------------------------------------------------------ --ORACLE ------------------------------------------------------------------------------------------ -- drop table testtab; create table testtab(id int,'aa'); insert into testtab(id,name)values(2,'bb'); select * from testtab; --方法一 alter table testtab add (addtime date default sysdate null);--字段历史数据自动填充 --方法二 alter table testtab add (addtime2 date null); alter table testtab modify (addtime2 date default sysdate); --字段历史数据为null /* ID NAME ADDTIME ADDTIME2 -- ---- ------------------ -------- 1 1 aa 2017/2/14 11:35:34 2 2 bb 2017/2/14 11:35:34 */ insert into testtab(id,'cc'); select * from testtab; /* ID NAME ADDTIME ADDTIME2 -- ---- ------------------ -------- 1 1 aa 2017/2/14 11:35:34 2 2 bb 2017/2/14 11:35:34 3 3 cc 2017/2/14 11:36:44 2017/2/14 11:36:44 */
