oracle ,mysql,sqlserver 字段设置默认值是否为空测试

最新推荐文章于 2022-10-24 19:58:38 发布

薛定谔的DBA

最新推荐文章于 2022-10-24 19:58:38 发布

阅读量1k

点赞数

分类专栏： DB_compare

本文链接：https://blog.csdn.net/kk185800961/article/details/55096689

版权

DB_compare 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

------------------------------------------------------------------------------------------
--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
*/