------------------------------------------------------------------------------------------
--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,name)values(1,'aa'),(2,'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()),(4,'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,name varchar(10));
insert into testtab(id,name)values(1,'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,name)values(3,'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
*/