mysql ‘’ 和null区别

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

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

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

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

“NULL columns require additional spacein the row to record whether their values are NULL. For MyISAMtables, each NULL column takes one bit extra, rounded up to thenearest 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 notnull，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 DEFAULTCHARSET = 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  bisnull;

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

| 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 ) NOTNULL ,
`b` timestamp NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP ,
PRIMARY KEY ( `a` )
) ENGINE = InnoDB DEFAULTCHARSET = 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

4 mysql可视化编辑区中，空指的是NULL，勾选空表示该字段允许为空，那么他的默认值是NULL，如果某字段不勾选空，表示该字段的值不为空，那么该字段的值就不能插入NULL