oracle ,mysql,sqlserver 字段设置默认值是否为空测试
发布时间:2020-12-12 18:48:10 所属栏目:百科 来源:网络整理
导读:--------------------------------------------------------------------------------------------MSSQL-------------------------------------------------------------------------------------------- drop table testtabcreate table testtab(id int,nam
------------------------------------------------------------------------------------------ --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 */ (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
推荐文章
站长推荐
热点阅读