Mysql --思考NULL

在创建表时,通过是否指定NOT NULL,可以指定列是否可以不包含值.当一个列不包含值时,称其为包含值NULL.NULL与字段包含0,空字符串或仅仅包含空格不同.下面开始对NULL进行讲解.

NOT NULL就一定不可以插入NULL吗?

答案是否定的.创建一下三个表进行验证.

CREATE TABLE `test_null_1` (
  `id` mediumint(8)  NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `test_null_2` (
  `id` mediumint(8)   AUTO_INCREMENT,
  `create_time` timestamp  ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `test_null_3` (
  `id` mediumint(8) not null  AUTO_INCREMENT,
 `gender` int not null,
  `name` char(22) not null,
`create_time` datetime not null,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在三个表中插入数据

insert into test_null_1 values(null,null);
insert into test_null_2 values(null,null);
insert into test_null_3(gender) values(null);
insert into test_null_3(name) values(null);
insert into test_null_3(create_time) values(null);

插入结果如图所示:

从以上插入结果中可以看出,如果列定义了NOT NULL属性,则是不允许插入NULL值的,若没有规定则可以插入NULL值.但是有两个特例:

1. 具有auto_increment属性的列,即使此列规定NOT NULL,仍然可以插入Null值,且插入成功
2. 时间类型为 Timestamp的列,即使此列规定NOT NULL,仍然可以插入Null值,且插入成功.(注意:并不是所有的时间类型都是特例,在test_null_3中验证了datetime类型如果规定NOT NULL,则不可以插入NULL值,date,time和datetime一样,都不是特例)

NULL不一定为NULL

插入成功的NULL在表中存储的就一定是NULL吗?
答案是否定的.
创建表test_null_4

CREATE TABLE `test_null_4` (
  `id` mediumint(8) not null  AUTO_INCREMENT,
 `gender` int ,
  `name` char(22) ,
 `create_time` datetime ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

insert into test_null_4(gender,name,create_time)values(null,null,null)

查询test_null_1,test_null_2,test_null_4表中数据,结果如图所示

除了两个特例,其他数据类型的列,如果允许插入NULL,且插入NULL的话,则在表中存储就是NULL.即NULL就是NULL
对于两个特例而言,插入NULL,但在表中显示的结果就不是NULL了:

1. 具有auto_increment属性的列,即使此列规定NOT NULL,如果往这属性的列中插入Null值的话，仍可以插入NULL成功,且系统会插入一个正整数序列。
2. 时间类型为 Timestamp的列,即使此列规定NOT NULL,如果往这属性的列中插入Null值的话，仍可以插入NULL成功,且系统会插入一个当前时间.
   为什么系统会默认插入一个当前时间呢?用show命令查询创建表的语句

#自己定义
CREATE TABLE `test_null_1` (
  `id` mediumint(8)  NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#系统补全
 CREATE TABLE `test_null_1` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

#自己定义
CREATE TABLE `test_null_2` (
  `id` mediumint(8)   AUTO_INCREMENT,
  `create_time` timestamp  ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#系统补全
 CREATE TABLE `test_null_2` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

从中可以看出,对于timestamp 类型,即使我们没有规定NOTNULL属性,系统也会帮我们补全,并设置一个当前时间的默认值.

列中到底插入了什么值?

在生成记录却没有插入此列值时,那么在这条记录中此列最终会插入什么值呢?
首先毫无疑问,具有auto_increment属性的列插入的永远都是以正整数.那么其他列呢?
插入一下数据(我删掉了之前插入的数据)

insert into test_null_1 (id)values(null);
insert into test_null_2 (id)values(null);
insert into test_null_3 (id)values(null);
insert into test_null_4 (id)values(null);

对于以上结果,在来看看系统帮我们自动补全了什么

#自己定义
CREATE TABLE `test_null_3` (
  `id` mediumint(8) not null  AUTO_INCREMENT,
 `gender` int not null,
  `name` char(22) not null,
`create_time` datetime not null,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#系统补全
CREATE TABLE `test_null_3` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `gender` int(11) NOT NULL,
  `name` char(22) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

#自己定义
CREATE TABLE `test_null_4` (
  `id` mediumint(8) not null  AUTO_INCREMENT,
 `gender` int ,
  `name` char(22) ,
 `create_time` datetime ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#系统补全
CREATE TABLE `test_null_4` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `gender` int(11) DEFAULT NULL,
  `name` char(22) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

从以上补全内容可以看出,当列没有规定NOT NULL也没有设置默认值时,系统会帮我们自动补全一个NULL默认值.也就是说,当列没有规定NOT NULL时,不管用户显示定义,还是系统自动补全,列都应该有且会有一个默认值.
当规定NOT NULL,用户也可以设置默认值,当用户没有设置时,系统默认值会根据不同数据类型而定,对于int,,会默认插入0;对于char,会默认插入空字符串;对于datetime,date,time三种时间数据类型,会默认插入零点, 0000-00-00 00:00:00
ps:从以上可以看出,对于四种时间类型,datetime,date,time,timestamp,
timestamp类型是多么与众不同.不管是否允许插入空值,都会默认插入当前时间.

NULL值检查

NULL 的条件比较运算比较特殊的,不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。需要使用IS NULL 和 IS NOT NULL 运算符。

聚集函数对于NULL的处理

SQL聚集函数,我们经常用到的有AVG(),COUNT(),MAX(),MIN(),SUM(),这几个函数都是忽略值为NULL的行的.

1.count()

CREATE TABLE `test_null_5` (
  `id` mediumint(8)  NOT NULL AUTO_INCREMENT,
  `age` int(11) default NULL,
  `name` char(22) default NULL,
  `create_time` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test_null_5 values(null, null,null,null);
insert into test_null_5 values(2, 20,'sunyan',now());
insert into test_null_5 values(null, 22,'',now());
insert into test_null_5 values(null, 0,'',now());
select * from test_null;
select count(age),count(name), count(*) from test_null;

从查询结果中可以看到,count()函数有两种使用方式,这两种方式对于NULL的处理略有不同.

1. count(*)是对表中 行的数目进行计数,女给不管表列中包含的是空值NULL还是非空值.
2. count(column)对特定列中具有值的行进行计数,忽略NULL值.

2.AVG()

从结果中可以看出,AVG()忽略值为NULL的行

反思一下

1. 最近因为NULL值,意识到平时一些习以为常的知识点,其实我并不能了解他.
2. 以后在写创建表SQL时,一定要按照系统会补全的样子写.像我以前可能会这样定义一张表.

CREATE TABLE `test_null_4` (
  `id` mediumint(8) not null  AUTO_INCREMENT,
 `gender` int ,
  `name` char(22) ,
 `create_time` datetime ,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对比系统补全语句,我发现自己在这样定义时,完全没有意识到gender会有一个默认值NULL.这就是一个我不了解的知识点.按照补全的写,充分了解每一列可以插入怎样的值.