MySQL一千个不用Null的理由

港真，Null 貌似在哪里都是个头疼的问题，比如 Java 里让人头疼的 NullPointerException，为了避免猝不及防的空指针异常，千百年来程序猿们不得不在代码里小心翼翼的各种 if 判断，麻烦而又臃肿，为此 java8 引入了 Optional 来避免这一问题。

十余年专注成都网站制作，成都企业网站建设，个人网站制作服务，为大家分享网站制作知识、方案，网站设计流程、步骤,成功服务上千家企业。为您提供网站建设,网站制作,网页设计及定制高端网站建设服务,专注于成都企业网站建设,高端网页制作,对展览展示等多个行业，拥有丰富的网站维护经验。

下面咱们要聊的是 MySQL 里的 null，在大量的 MySQL 优化文章和书籍里都提到了字段尽可能用NOT NULL，而不是NULL，除非特殊情况。但却都只给结论不说明原因，犹如鸡汤不给勺子一样，让不少初学者对这个结论半信半疑或者云里雾里。本文今天就详细的剖析下使用 Null 的原因，并给出一些不用 Null 的理由。

1、NULL 为什么这么多人用?

NULL是创建数据表时默认的，初级或不知情的或怕麻烦的程序员不会注意这点。

很多人员都以为not null 需要更多空间，其实这不是重点。

重点是很多程序员觉得NULL在开发中不用去判断插入数据，写sql语句的时候更方便快捷。

2、是不是以讹传讹?

MySQL 官网文档：

  • NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。

—— 出自《高性能mysql第二版》

照此分析，还真不是以讹传讹，这是有理论依据和出处的。

3、给我一个不用 Null 的理由?

  • (1)所有使用NULL值的情况，都可以通过一个有意义的值的表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。
  • (2)NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。

注意：但把NULL列改为NOT NULL带来的性能提示很小，除非确定它带来了问题，否则不要把它当成优先的优化措施，最重要的是使用的列的类型的适当性。

  • (3)NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp
  • (4)NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错

 

 
 
 
    
  
  
  
  1. create table table_2 ( 
    2. 
  
  
  
  2.      `id` INT (11) NOT NULL, 
    3. 
  
  
  
  3.     user_name varchar(20) NOT NULL 
    4. 
  
  
  
  4. ) 
    5. 
  
  
  
  5.  
    6. 
  
  
  
  6.  
    7. 
  
  
  
  7. create table table_3 ( 
    8. 
  
  
  
  8.      `id` INT (11) NOT NULL, 
    9. 
  
  
  
  9.     user_name varchar(20) 
    10. 
  
  
  
  10. ) 
    11. 
  
  
  
  11.  
    12. 
  
  
  
  12. insert into table_2 values (4,"zhaoliu_2_1"),(2,"lisi_2_1"),(3,"wangmazi_2_1"),(1,"zhangsan_2"),(2,"lisi_2_2"),(4,"zhaoliu_2_2"),(3,"wangmazi_2_2") 
    13. 
  
  
  
  13.  
    14. 
  
  
  
  14. insert into table_3 values (1,"zhaoliu_2_1"),(2, null) 
    15. 
  
  
  
  15.  
    16. 
  
  
  
  16. -- 1、NOT IN子查询在有NULL值的情况下返回永远为空结果，查询容易出错 
    17. 
  
  
  
  17. select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1) 
    18. 
  
  
  
  18.  
    19. 
  
  
  
  19. mysql root@10.48.186.32:t_test_zz5431> select user_name from table_2 where user_name not 
    20. 
  
  
  
  20.                                     -> in (select user_name from table_3 where id!=1); 
    21. 
  
  
  
  21. +-------------+ 
    22. 
  
  
  
  22. | user_name   | 
    23. 
  
  
  
  23. |-------------| 
    24. 
  
  
  
  24. +-------------+ 
    25. 
  
  
  
  25. 0 rows in set 
    26. 
  
  
  
  26. Time: 0.008s 
    27. 
  
  
  
  27. mysql root@10.48.186.32:t_test_zz5431> 
    28. 
  
  
  
  28.  
    29. 
  
  
  
  29. -- 2、单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集 
    30. 
  
  
  
  30. -- 如果name允许为null，索引不存储null值，结果集中不会包含这些记录。所以，请使用not null约束以及默认值。 
    31. 
  
  
  
  31. select * from table_3 where name != 'zhaoliu_2_1' 
    32. 
  
  
  
  32.  
    33. 
  
  
  
  33. -- 3、如果在两个字段进行拼接：比如题号+分数，首先要各字段进行非null判断，否则只要任意一个字段为空都会造成拼接的结果为null。 
    34. 
  
  
  
  34. select CONCAT("1",null) from dual; -- 执行结果为null。 
    35. 
  
  
  
  35.  
    36. 
  
  
  
  36. -- 4、如果有 Null column 存在的情况下，count(Null column)需要格外注意，null 值不会参与统计。 
    37. 
  
  
  
  37. mysql root@10.48.186.32:t_test_zz5431> select * from table_3; 
    38. 
  
  
  
  38. +------+-------------+ 
    39. 
  
  
  
  39. |   id | user_name   | 
    40. 
  
  
  
  40. |------+-------------| 
    41. 
  
  
  
  41. |    1 | zhaoliu_2_1 | 
    42. 
  
  
  
  42. |    2 |       | 
    43. 
  
  
  
  43. |   21 | zhaoliu_2_1 | 
    44. 
  
  
  
  44. |   22 |       | 
    45. 
  
  
  
  45. +------+-------------+ 
    46. 
  
  
  
  46. 4 rows in set 
    47. 
  
  
  
  47. Time: 0.007s 
    48. 
  
  
  
  48. mysql root@10.48.186.32:t_test_zz5431> select count(user_name) from table_3; 
    49. 
  
  
  
  49. +--------------------+ 
    50. 
  
  
  
  50. |   count(user_name) | 
    51. 
  
  
  
  51. |--------------------| 
    52. 
  
  
  
  52. |                  2 | 
    53. 
  
  
  
  53. +--------------------+ 
    54. 
  
  
  
  54. 1 row in set 
    55. 
  
  
  
  55. Time: 0.007s 
    56. 
  
  
  
  56.  
    57. 
  
  
  
  57. -- 5、注意 Null 字段的判断方式， = null 将会得到错误的结果。 
    58. 
  
  
  
  58. mysql root@localhost:cygwin> create index IDX_test on table_3 (user_name); 
    59. 
  
  
  
  59. Query OK, 0 rows affected 
    60. 
  
  
  
  60. Time: 0.040s 
    61. 
  
  
  
  61. mysql root@localhost:cygwin>  select * from table_3 where user_name is null\G 
    62. 
  
  
  
  62. ***************************[ 1. row ]*************************** 
    63. 
  
  
  
  63. id        | 2 
    64. 
  
  
  
  64. user_name | None 
    65. 
  
  
  
  65.  
    66. 
  
  
  
  66. 1 row in set 
    67. 
  
  
  
  67. Time: 0.002s 
    68. 
  
  
  
  68. mysql root@localhost:cygwin> select * from table_3 where user_name = null\G 
    69. 
  
  
  
  69.  
    70. 
  
  
  
  70. 0 rows in set 
    71. 
  
  
  
  71. Time: 0.002s 
    72. 
  
  
  
  72. mysql root@localhost:cygwin> desc select * from table_3 where user_name = 'zhaoliu_2_1'\G 
    73. 
  
  
  
  73. ***************************[ 1. row ]*************************** 
    74. 
  
  
  
  74. id            | 1 
    75. 
  
  
  
  75. select_type   | SIMPLE 
    76. 
  
  
  
  76. table         | table_3 
    77. 
  
  
  
  77. type          | ref 
    78. 
  
  
  
  78. possible_keys | IDX_test 
    79. 
  
  
  
  79. key           | IDX_test 
    80. 
  
  
  
  80. key_len       | 23 
    81. 
  
  
  
  81. ref           | const 
    82. 
  
  
  
  82. rows          | 1 
    83. 
  
  
  
  83. Extra         | Using where 
    84. 
  
  
  
  84.  
    85. 
  
  
  
  85. 1 row in set 
    86. 
  
  
  
  86. Time: 0.006s 
    87. 
  
  
  
  87. mysql root@localhost:cygwin> desc select * from table_3 where user_name = null\G 
    88. 
  
  
  
  88. ***************************[ 1. row ]*************************** 
    89. 
  
  
  
  89. id            | 1 
    90. 
  
  
  
  90. select_type   | SIMPLE 
    91. 
  
  
  
  91. table         | None 
    92. 
  
  
  
  92. type          | None 
    93. 
  
  
  
  93. possible_keys | None 
    94. 
  
  
  
  94. key           | None 
    95. 
  
  
  
  95. key_len       | None 
    96. 
  
  
  
  96. ref           | None 
    97. 
  
  
  
  97. rows          | None 
    98. 
  
  
  
  98. Extra         | Impossible WHERE noticed after reading const tables 
    99. 
  
  
  
  99.  
    100. 
  
  
  
  100. 1 row in set 
    101. 
  
  
  
  101. Time: 0.002s 
    102. 
  
  
  
  102. mysql root@localhost:cygwin> desc select * from table_3 where user_name is null\G 
    103. 
  
  
  
  103. ***************************[ 1. row ]*************************** 
    104. 
  
  
  
  104. id            | 1 
    105. 
  
  
  
  105. select_type   | SIMPLE 
    106. 
  
  
  
  106. table         | table_3 
    107. 
  
  
  
  107. type          | ref 
    108. 
  
  
  
  108. possible_keys | IDX_test 
    109. 
  
  
  
  109. key           | IDX_test 
    110. 
  
  
  
  110. key_len       | 23 
    111. 
  
  
  
  111. ref           | const 
    112. 
  
  
  
  112. rows          | 1 
    113. 
  
  
  
  113. Extra         | Using where 
    114. 
  
  
  
  114.  
    115. 
  
  
  
  115. 1 row in set 
    116. 
  
  
  
  116. Time: 0.002s 
    117. 
  
  
  
  117. mysql root@localhost:cygwin> 
    118.

(5)Null 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位

 
 
 
    
  
  
  
  1. alter table table_3 add index idx_user_name (user_name); 
    2. 
  
  
  
  2. alter table table_2 add index idx_user_name (user_name); 
    3. 
  
  
  
  3. explain select * from table_2 where user_name='zhaoliu_2_1'; 
    4. 
  
  
  
  4. explain select * from table_3 where user_name='zhaoliu_2_1'; 
    5.

 

可以看到同样的 varchar(20) 长度，table_2 要比 table_3 索引长度大，这是因为：

  • 两张表的字符集不一样，且字段一个为 NULL 一个非 NULL。

 

key_len 的计算规则和三个因素有关：数据类型、字符编码、是否为 NULL

key_len 62 == 20*3(utf8 3字节) + 2 (存储 varchar 变长字符长度 2字节，定长字段无需额外的字节)

key_len 83 == 20*4(utf8mb4 4字节) + 1 (是否为 Null 的标识) + 2 (存储 varchar 变长字符长度 2字节，定长字段无需额外的字节)

所以说索引字段***不要为NULL，因为NULL会使索引、索引统计和值更加复杂，并且需要额外一个字节的存储空间。基于以上这些理由和原因，我想咱们不用 Null 的理由应该是够了

分享名称：MySQL一千个不用Null的理由
当前URL：http://www.gawzjz.com/qtweb/news17/199217.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

响应式网站知识

分类信息网

虚拟主机购买　　 　手机网站建设　　 　成都网站推广营销　　 　成都网页制作　　 　东电技服　　 　成都租用服务器　　 　普格博源建站　　 　成都雪糕加盟　　 　重庆电商网站定制　　 　四川成都网站制作　　 　成都400电话　　 　大英泰恒网站　　 　云服务器香港　　 　宜宾做网站　　 　led无边发光字　　 　腾讯云免备案主机　　 　重庆建站　　 　温江做网站　　 　响应式网站　　 　吉安网站建设