日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢(xún)
選擇下列產(chǎn)品馬上在線(xiàn)溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問(wèn)題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷(xiāo)解決方案
MySQL一千個(gè)不用Null的理由

港真，Null 貌似在哪里都是個(gè)頭疼的問(wèn)題，比如 Java 里讓人頭疼的 NullPointerException，為了避免猝不及防的空指針異常，千百年來(lái)程序猿們不得不在代碼里小心翼翼的各種 if 判斷，麻煩而又臃腫，為此 java8 引入了 Optional 來(lái)避免這一問(wèn)題。

十余年專(zhuān)注成都網(wǎng)站制作，成都企業(yè)網(wǎng)站建設(shè)，個(gè)人網(wǎng)站制作服務(wù)，為大家分享網(wǎng)站制作知識(shí)、方案，網(wǎng)站設(shè)計(jì)流程、步驟,成功服務(wù)上千家企業(yè)。為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù),專(zhuān)注于成都企業(yè)網(wǎng)站建設(shè),高端網(wǎng)頁(yè)制作,對(duì)展覽展示等多個(gè)行業(yè)，擁有豐富的網(wǎng)站維護(hù)經(jīng)驗(yàn)。

下面咱們要聊的是 MySQL 里的 null，在大量的 MySQL 優(yōu)化文章和書(shū)籍里都提到了字段盡可能用NOT NULL，而不是NULL，除非特殊情況。但卻都只給結(jié)論不說(shuō)明原因，猶如雞湯不給勺子一樣，讓不少初學(xué)者對(duì)這個(gè)結(jié)論半信半疑或者云里霧里。本文今天就詳細(xì)的剖析下使用 Null 的原因，并給出一些不用 Null 的理由。

1、NULL 為什么這么多人用?

NULL是創(chuàng)建數(shù)據(jù)表時(shí)默認(rèn)的，初級(jí)或不知情的或怕麻煩的程序員不會(huì)注意這點(diǎn)。

很多人員都以為not null 需要更多空間，其實(shí)這不是重點(diǎn)。

重點(diǎn)是很多程序員覺(jué)得NULL在開(kāi)發(fā)中不用去判斷插入數(shù)據(jù)，寫(xiě)sql語(yǔ)句的時(shí)候更方便快捷。

2、是不是以訛傳訛?

MySQL 官網(wǎng)文檔：

  • 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難以?xún)?yōu)化引用可空列查詢(xún)，它會(huì)使索引、索引統(tǒng)計(jì)和值更加復(fù)雜。可空列需要更多的存儲(chǔ)空間，還需要mysql內(nèi)部進(jìn)行特殊處理?？煽樟斜凰饕?，每條記錄都需要一個(gè)額外的字節(jié)，還能導(dǎo)致MYisam 中固定大小的索引變成可變大小的索引。

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

照此分析，還真不是以訛傳訛，這是有理論依據(jù)和出處的。

3、給我一個(gè)不用 Null 的理由?

  • (1)所有使用NULL值的情況，都可以通過(guò)一個(gè)有意義的值的表示，這樣有利于代碼的可讀性和可維護(hù)性，并能從約束上增強(qiáng)業(yè)務(wù)數(shù)據(jù)的規(guī)范性。
  • (2)NULL值到非NULL的更新無(wú)法做到原地更新，更容易發(fā)生索引分裂，從而影響性能。

注意：但把NULL列改為NOT NULL帶來(lái)的性能提示很小，除非確定它帶來(lái)了問(wèn)題，否則不要把它當(dāng)成優(yōu)先的優(yōu)化措施，最重要的是使用的列的類(lèi)型的適當(dāng)性。

  • (3)NULL值在timestamp類(lèi)型下容易出問(wèn)題，特別是沒(méi)有啟用參數(shù)explicit_defaults_for_timestamp
  • (4)NOT IN、!= 等負(fù)向條件查詢(xún)?cè)谟?NULL 值的情況下返回永遠(yuǎn)為空結(jié)果，查詢(xún)?nèi)菀壮鲥e(cuò)

 

 
 
 
    
  
  
  
  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子查詢(xún)?cè)谟蠳ULL值的情況下返回永遠(yuǎn)為空結(jié)果，查詢(xún)?nèi)菀壮鲥e(cuò) 
    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值，復(fù)合索引不存全為null的值，如果列允許為null，可能會(huì)得到“不符合預(yù)期”的結(jié)果集 
    30. 
  
  
  
  30. -- 如果name允許為null，索引不存儲(chǔ)null值，結(jié)果集中不會(huì)包含這些記錄。所以，請(qǐng)使用not null約束以及默認(rèn)值。 
    31. 
  
  
  
  31. select * from table_3 where name != 'zhaoliu_2_1' 
    32. 
  
  
  
  32.  
    33. 
  
  
  
  33. -- 3、如果在兩個(gè)字段進(jìn)行拼接：比如題號(hào)+分?jǐn)?shù)，首先要各字段進(jìn)行非null判斷，否則只要任意一個(gè)字段為空都會(huì)造成拼接的結(jié)果為null。 
    34. 
  
  
  
  34. select CONCAT("1",null) from dual; -- 執(zhí)行結(jié)果為null。 
    35. 
  
  
  
  35.  
    36. 
  
  
  
  36. -- 4、如果有 Null column 存在的情況下，count(Null column)需要格外注意，null 值不會(huì)參與統(tǒng)計(jì)。 
    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 將會(huì)得到錯(cuò)誤的結(jié)果。 
    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 列需要更多的存儲(chǔ)空間：需要一個(gè)額外字節(jié)作為判斷是否為 NULL 的標(biāo)志位

 
 
 
    
  
  
  
  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) 長(zhǎng)度，table_2 要比 table_3 索引長(zhǎng)度大，這是因?yàn)椋?/p>

  • 兩張表的字符集不一樣，且字段一個(gè)為 NULL 一個(gè)非 NULL。

 

key_len 的計(jì)算規(guī)則和三個(gè)因素有關(guān)：數(shù)據(jù)類(lèi)型、字符編碼、是否為 NULL

key_len 62 == 20*3(utf8 3字節(jié)) + 2 (存儲(chǔ) varchar 變長(zhǎng)字符長(zhǎng)度 2字節(jié)，定長(zhǎng)字段無(wú)需額外的字節(jié))

key_len 83 == 20*4(utf8mb4 4字節(jié)) + 1 (是否為 Null 的標(biāo)識(shí)) + 2 (存儲(chǔ) varchar 變長(zhǎng)字符長(zhǎng)度 2字節(jié)，定長(zhǎng)字段無(wú)需額外的字節(jié))

所以說(shuō)索引字段***不要為NULL，因?yàn)镹ULL會(huì)使索引、索引統(tǒng)計(jì)和值更加復(fù)雜，并且需要額外一個(gè)字節(jié)的存儲(chǔ)空間?；谝陨线@些理由和原因，我想咱們不用 Null 的理由應(yīng)該是夠了


文章名稱(chēng)：MySQL一千個(gè)不用Null的理由
分享URL：http://m.5511xx.com/article/dhhhhop.html