开发者

MySQL中NULL和空值的区别及说明

目录
  • mysql中NULL和空值的区别
    • 简介
    • 区别
    • 区别验证
  • 总结

    MySQL中NULL和空值的区别

    简介

    NULL也就是在字段中存储NULL值,空值也就是字段中存储空字符(’’)。 

    区别

    1、空值不占空间,NULL值占空间。当字段不为NULL时,也可以插入空值。

    2、当使用 IS NOT NULL 或者 IS NULL 时,只能查出字段中没有不为NULL的或者为 NULL 的,不能查出空值。

    3、判断NULL 用IS NULL 或者 is not null,SQL 语句函数中可以使用IFNULL()函数来进行处理,判断空字符用 =’‘或者<>’'来进行处理。

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

    5、MySql中如果某一列中含有NULL,那么包含该列的索引就无效了。这一句不是很准确。

    6:实际到底是使用NULL值还是空值(’’),根据实际业务来进行区分。个人建议在实际开发中如果没有特殊的业务场景,可以直接使用空值。

    区别验证

    1、占用空间区别

    mysql>  select length(NULL), length(''), length('1');
    +--------------+------------+-------------+
    | length(NULL) | length('') | length('1') |
    +--------------+------------+-------------+
    | NULL         |          0 |           1 |
    +--------------+------------+-------------+
    1 row in set
    

    通俗的讲:

    空值就像是一个真空转态杯子,什么都没有,而NULL值就是一个装满空气的杯子,虽然看起来都是一样的,但是有着本质的区别。

    NULL columns require additional space in the row to record whether their values are NULL.

    NULL列需要行中的额外空间来记录它们的值是否为NULL。

    小结:从上面看出空值(’’)的长度是0,是不占用空间的;而的NULL长度是NULL,其实它是占用空间的,看下面说明。

    2、插入/查询方式区别

    CREATE TABLE `thttp://www.devze.comb_test` (
      `one` varchar(10) NOT NULL,
      `two` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- 全部插入 NULL,失败
    mysql> INSERT tb_test VALUES (NULL,NULL);
    1048 - Column 'one' cannot be null
    
    
    -- 全部插入 空值,成功
    mysql> INSERT tb_test VALUES ('','');
    Query OK, 1 row affected
    
    -- 模拟数据:
    INSERT tb_test VALUES (1,NULL);
    INSERT tb_test VALUES ('',2);
    INSERT tb_test VALUES (3,3);
    `
    
    --空值字段:
    -- 使用 is null/is not null
    mysql> SELECT * FROM tb_test where one is NULL;
    Empty set
    
    mysql> SELECT * FROM tb_test where one is not NULL;
    +-----+------+
    | one | two  |
    +-----+------+
    | 1   | NULL |
    |     | 2    |
    | 3   | 3    |
    +-----+------+
    3 rows in set
    -- 使用 = 、!=
    mysql> SELECT * FROM tb_test where one = '';
    +-----+-----+
    | one | two |
    +-----+-----+
    |     | 2   |
    +-----+-----javascript+
    1 row in set
    
    mysql> SELECT * FROM tb_test where one != '';
    +-----+------+
    | one | two  |
    +-----+------+
    | 1   | NULL |
    | 3   | 3    |
    +-----+------+
    2 rows in set
    
    
    --NULL值字段:
    
    -- 使用 is null/is not null
    mysql> SELECT * FROM tb_test where two is not NULL;
    +-----+-----+
    | one | two |
    +-----+-----+
    |     | 2   |
    | 3   | 3   |
    +-----+-----+
    2 rows in set
    
    mysql> SELECT * FROM tb_test where two is NULL;
    +-----+------+
    | one | two  |
    +-----+------+
    | 1   | NULL |
    +-----+------+
    1 row in set
    
    -- 使用 = 、!=
    mysql> SELECT * FROM tb_test where two = '';
    Empty set
    
    mysql> SELECT * FROM tb_test where two != '';
    +-----+-----+
    | one | two |
    +-----+-----+
    |     | 2   |
    | 3   | 3   |
    +-----+-----+
    2 rows in set
    
    
    
    

    小结:

    如果要单纯查NULL值列,则使用 is NULL去查,单纯去查空值(’’)列,则使用 =’’。

    建议查询方式:NULL值查询使用is null/is not null查询,而空值(’’)可以使用=或者!=、<、>等算术运算符。

    3、COUNT 和 IFNULL函数

    使用COUNT函数:

    mysql> SandroidELECT count(one) FROM tb_test;
    +------------+
    | count(one) |
    +------------+
    |          3 |
    +------------+
    1 row in set
    
    mysql> SELECT count(two) FROM tb_test;
    +------------+
    | count(two) |
    +------------+
    |          2 |
    +------------+
    1 row in set
    
    mysql> SELECT count(*) FROM tb_test;
    +----------+
    | count(*) |
    +----------+
    |        3 |
    +----------+
    1 row in set
    

    使用IFNULL函数:

    mysql> SELECT IFNULL(one,111111111) from tb_test WHERE one = '';
    +-----------------------+
    | IFNULL(one,111111111) |
    +-----------------------+
    |                       |
    +-----------------------+
    1 row in set
    
    mysql> SELECT IFNULL(two,11111111) from tb_test where two is NULL;
    +----------------------+
    | IFNULL(two,11111111) |
    +----------------------+
    | 11111111             |
    +----------------------+
    1 row in set
    

    小结:使用 COUNT(字段) 统计会过滤掉 NULL 值,但是不会过滤掉空值。

    说明:

    IFNULL有两个参数。如果第一个参数字段不是NULL,http://www.devze.com则返回第一个字段的值。

    否则,IFNULL函数返回第二个参数的值(默认值)。

    4、索引字段说明

    看到网上有一些人说: MySql中如果某一列中含有NULL,那么包含该列的索引就无效http://www.devze.com了。

    一个普通索引,一个复合索引。

    复合索引遵守“最左前缀”原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。

    -- ALTER TABLE table_name ADD INDEX index_name(col_name);
    ALTER TABLE tb_test ADD INDEX index_oat (one, two);
    ALTER TABLE tb_test add INDEX index_two(two);
    

    使用 show keys from 表名;或show indexes from 表名; ,查看这个表的所有索引信息。

    MySQL中NULL和空值的区别及说明

    复合索引

    MySQL中NULL和空值的区别及说明

    普通索引

    MySQL中NULL和空值的区别及说明

    发现查询two字段 是可以正常使用索引的。我使用的MYSQL 5.7 ,InnoDB 引擎。MySQL可以在含有null的列上使用索引。可能是其他条件下不行。

    小结:在有NULL值得字段上使用常用的索引,如普通索引、复合索引、全文索引等不会使索引失效。在官网查看在空间索引的情况下,说明了 索引列必须为NOT NULL。

    附官网查看

    MySQL中NULL和空值的区别及说明

    总结

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持编程客栈(www.devze.com)。

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新数据库

    数据库排行榜