MySQL 中NULL和空值的区别（转载 http://blog.sina.com.cn/s/blog_3f2a82610102v4dn.html）

时间：2023-03-08 16:31:57

MySQL 中NULL和空值的区别（转载 http://blog.sina.com.cn/s/blog_3f2a82610102v4dn.html）

平时我们在使用MySQL的时候，对于MySQL中的NULL值和空值区别不能很好的理解。注意到NULL值是未知的，且占用空间，不走索引，DBA建议建表的时候最好设置字段是NOT NULL 来避免这种低效率的事情的发生。

问题 1：首先，我们需要搞清楚 "空值" 和"NULL"的概念：

1：空值('')是不占用空间的

2: MySQL中的NULL其实是占用空间的。官方文档说明:

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

长度验证：注意空值的''之间是没有空格的。

mysql> select length(''),length(null),length(' ');

+------------+--------------+--------------+

| length('') | length(null) | length(' ') |

+------------+--------------+--------------+

| 0 | NULL | 2 |

+------------+--------------+--------------+

问题2：

判断字段不为空的时候，查询语句到底是用 select * from tablename where columnname <> '' 还是用

select * from tablename where column is not null，2个查询语句有啥不同。

eg:

mysql> show create table testaa;

+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| testaa | CREATE TABLE `testaa` (

`a` int(11) NOT NULL,

`b` varchar(20) DEFAULT NULL,

`c` varchar(20) NOT NULL,

PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

插入测试数据:

mysql> insert testaa values (1,'aa','');

Query OK, 1 row affected (0.00 sec)

mysql> insert testaa values (2,'','');

Query OK, 1 row affected (0.00 sec)

mysql> insert testaa values (3,null,'');

Query OK, 1 row affected (0.00 sec)

mysql> insert testaa values (4,NULL,'');

Query OK, 1 row affected (0.00 sec)

mysql> insert testaa values (5,'aafa','fa');

Query OK, 1 row affected (0.00 sec)

mysql> insert testaa values (6,'',NULL);
ERROR 1048
(23000): Column 'c' cannot be null

mysql>
select *
from testaa;

+---+------+----+

| a
| b
| c |

+---+------+----+

| 1
| aa
| |

| 2
|
|
|

| 3
| NULL
|
|

| 4
| NULL
|
|

| 5
| aafa
| fa |

+---+------+----+

查询验证过程:

mysql>
select *
from testaa
where c
is not
null;

+---+------+----+

| a
| b
| c |

+---+------+----+

| 1
| aa
| |

| 2
|
|
|

| 3
| NULL
|
|

| 4
| NULL
|
|

| 5
| aafa
| fa |

+---+------+----+

5 rows
in set
(0.00
sec)

mysql>
select *
from testaa
where c
<> '';

+---+------+----+

| a
| b
| c |

+---+------+----+

| 5
| aafa
| fa |

+---+------+----+

1 row
in set
(0.00
sec)

mysql>
select *
from testaa
where
c = '';

+---+------+---+

| a
| b
| c |

+---+------+---+

| 1
| aa
| |

| 2
|
|
|

| 3
| NULL
| |

| 4
| NULL
| |

+---+------+---+

4 rows
in set
(0.00
sec)

mysql> select * from testaa where c
is null;

Empty
set (0.00 sec)

mysql>
select *
from testaa
where b
is not
null;

+---+------+----+

| a
| b
| c |

+---+------+----+

| 1
| aa
| |

| 2
|
|
|

| 5
| aafa
| fa |

+---+------+----+

3 rows
in set
(0.00
sec)

mysql>
select *
from testaa
where b
<> '';

+---+------+----+

| a
| b
| c |

+---+------+----+

| 1
| aa
| |

| 5
| aafa
| fa |

+---+------+----+

2 rows
in set
(0.00
sec)

mysql>
select *
from testaa
where b
='';

+---+------+---+

| a
| b
| c |

+---+------+---+

| 2
|
|
|

+---+------+---+

1 row
in set
(0.00
sec)

mysql> select * from testaa where b
is null;

+---+------+---+

| a
| b
| c |

+---+------+---+

| 3
| NULL
| |

| 4
| NULL
| |

+---+------+---+

mysql>
select length(b),length(c) from testaa;
+-----------+-----------+
| length(b) |
length(c)
|
+-----------+-----------+
| 2 | 0 |
| 0 | 0 |
| NULL
| 0 |
| NULL
| 0 |
| 4 | 2 |
+-----------+-----------+
5 rows
in set
(0.00
sec)

mysql>
select count(b),count(c) from testaa;
+----------+----------+
| count(b) |
count(c)
|
+----------+----------+
| 3 | 5 |
+----------+----------+
1 row
in set
(0.00
sec)

mysql>
create table testbb ( a int primary key , b timestamp);
Query OK, 0 rows affected (0.07 sec)

mysql>
show create table testbb;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table
| Create
Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| testbb
| CREATE TABLE
`testbb` (
`a` int(11) NOT
NULL,
`b` timestamp NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql>
insert into
testbb vales (1,null) ;

mysql>
insert into
testbb values (2,'');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
show warnings;
+---------+------+----------------------------------------+

| Level
| Code
| Message
|
+---------+------+----------------------------------------+

| Warning
| 1265
| Data
truncated for
column 'b'
at row 1
|
+---------+------+----------------------------------------+

mysql>
select *
from testbb;
+---+---------------------+
| a | b |
+---+---------------------+
| 1 | 2014-08-15 14:32:10 |
| 2 | 0000-00-00 00:00:00 |
+---+---------------------+
2 rows
in set
(0.00
sec)

注意事项：

1：在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值是会进行统计到其中的。

2：
判断NULL
用IS NULL
或者 is
not null,SQL 语句函数中可以使用ifnull()函数来进行处理，判断空字符用
=''或者 <>''来进行处理

3:
对于MySQL特殊的注意事项，对于timestamp数据类型，如果往这个数据类型插入的列插入NULL值，则出现的值是当前系统时间。插入空值，则会出现
'0000-00-00
00:00:00'

4：对于空值的判断到底是使用is null 还是 =''要根据实际业务来进行区分。

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。

首先，我们要搞清楚“空值” 和 “NULL” 的概念：
1、空值是不占用空间的
2、MySQL中的NULL其实是占用空间的

所谓的NULL就是什么都没有，连\0都没有，\0在字符串中是结束符，但是在物理内存是占空间的，等于一个字节，而NULL就是连这一个字节都没
有。在数据库里是严格区分的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。

数据库的字段ID设为NOT NULL, 仅仅说明该字段不能为NULL, 也就是说只有在
INSERT INTO table(ID) VALUES(NULL);
这种情况下数据库会报错，而
INSERT INTO table(ID) VALUES( ‘ ‘);
这不能说明是NULL, 数据库系统会根据ID设的缺省值填充，或者如果是自增字段就自动加一等缺省操作。

尽量避免NULL：应该指定列为NOT
NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为
NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

