MYSQL中NULL空值与空字符串区别详解及代码
"空值"是对null值的中文叫法,两者同指一个东西。空字符串即长度为零的字符串,在代码里"零长度字符串"用一对没有间隔的英文引号''表示,它的数据类型是明确的即属于字符型。
很多朋友会错误的认为NULL与空字符串’’是相同的。这看似是一件不重要的事情,但是在MySQL中,这两者是完全不同的。NULL是指没有值,而’’则表示值是存在的,只不过是长度为零。
下面我们实际建立一个表来进行测试和区分。
CREATE TABLE `tb_test` (
`name` varchar(255) DEFAULT NULL,
`value` varchar(255) DEFAULT NULL,
`test` char(0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
test列char(0)可以保存空值(NULL)或长度为零的字符串(空字符串)。
插入表中几条数据:
insert into tb_test values('t1',null,null); ##创建一条value为空的数据
insert into tb_test values('t2','',''); ##创建一条为空字符的数据
insert into tb_test values('t2','111','');
insert into tb_test values('t2','111',null);
- 对NULL的判断
用isnull判断是否为空:只有value为null 的时候 ISNULL(exp) 函数的返回值为1 ,空串和有数据都为0;
select * from tb_test where value is null;
select * from tb_test where ISNULL(value)
查询结果:
select * from tb_test where value is not null;
或select * from tb_test where ISNULL(value)=0
查询结果:
select * from tb_test where test is not null; -- test特殊字段查询
select * from tb_test where ISNULL(test)=0
2、对空字符串的判断
select * from tb_test where LENGTH(value)=0;
select * from tb_test where value='';
查询结果
select * from tb_test where LENGTH(test)=0;
select * from tb_test where test='';
查询结果:
3、同时剔除null 和 空字符串
select * from tb_test where ISNULL(value)=0 and LENGTH(trim(value))>0;
select * from tb_test where value is not null and value <>'';
4、对NULL和空字符串的替换
主要用这两个函数ifnull(目标字段,替换值) & COALESCE (目标字段,替换值)
select name,IFNULL(value,'ddd') from tb_test;
select name,COALESCE(value,'ddd') from tb_test;
查询结果:
同时替换两个
select name, case when (value='' or value is null) then 'ddd' else value end as value from tb_test;
5、在SQL中,NULL值与任何其它值的比较(即使是NULL)永远不会为“真”。
包含NULL的表达式总是会导出NULL值,除非在关于操作符的文档中以及表达式的函数中作了其他规定。下述示例中的所有列均返回NULL:
SELECT NULL, 1+NULL, CONCAT('test',NULL);
如果打算搜索列值为NULL的列,不能使用expr = NULL测试。下述语句不返回任何行,这是因为,对于任何表达式,expr = NULL永远不为“真”:
SELECT * FROM tb_test WHERE value = NULL;
另外MySQL数据库中执行SQL语句,需要小心几个陷阱:
1、空值不一定为空
空值是一个比较特殊的字段。在MySQL数据库中,在不同的情形下,空值往往代表不同的含义。这是MySQL数据库的一种特性。如在普通的字段中(字符型的数据),空值就是表示空值。但是如果将一个空值的数据插入到TimesTamp类型的字段中,空值就不一定为空。
在MySQL数据库中,NULL对于一些特殊类型的列来说,其代表了一种特殊的含义,而不仅仅是一个空值。对于这些特殊类型的列,各位读者主要是要记住两个。一个就是笔者上面举的TimesTamp数据类型。如果往这个数据类型的列中插入Null值,则其代表的就是系统的当前时间。另外一个是具有auto_increment属性的列。如果往这属性的列中插入Null值的话,则系统会插入一个正整数序列。而如果在其他数据类型中,如字符型数据的列中插入Null的数据,则其插入的就是一个空值。
2、Count等统计函数,在空值上也有特殊的应用。
使用DISTINCT、GROUP BY或ORDER BY时,所有NULL值将被视为等同的。
使用ORDER BY时,首先将显示NULL值,如果指定了DESC按降序排列,NULL值将最后显示。
SELECT * FROM tb_test order by value;
SELECT * FROM tb_test order by value desc;
对于聚合(累计)函数,如COUNT()、MIN()和SUM(),将忽略NULL值。
对此的例外是COUNT(*),它将计数行而不是单独的列值。例如,下述语句产生两个计数。首先计数表中的行数,其次计数value列中的非NULL值数目:
代码如下:
SELECT COUNT(*), COUNT(value) FROM tb_test;
3、可为NULL的列使得索引、索引统计和值比较都更复杂,可以NULL的列会使用更多的存储空间,在MYSQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节。因为可为NULL是列的默认属性,通常情况下最好指定列为NOT NULL,除非真的的需要存储NULL值。
实战示例,解疑答惑。
--不间端地思考,实时地批判你的工作!