對(duì)于MySQL的一些個(gè)規(guī)范，某些公司建表規(guī)范中有一項(xiàng)要求是所有字段非空，意味著沒有值的時(shí)候存儲(chǔ)一個(gè)默認(rèn)值。其實(shí)所有字段非空這么說應(yīng)該是絕對(duì)了，應(yīng)該說是盡可能非空，某些情況下不可能給出一個(gè)默認(rèn)值。那么這條要求，是基于哪些考慮因素，存儲(chǔ)空間？相關(guān)增刪查改操作的性能？亦或是其他考慮？該理論到底有沒有道理或者可行性，本文就個(gè)人的理解，做一個(gè)粗淺的分析。

1，基于存儲(chǔ)的考慮

這里對(duì)存儲(chǔ)的分析要清楚MySQL數(shù)據(jù)行的存儲(chǔ)格式，這里直接從這篇文章白嫖一部分結(jié)論，文章里分析的非常清楚（其實(shí)也是參考《MySQL技術(shù)內(nèi)容Innodb存儲(chǔ)引擎》）。對(duì)于默認(rèn)的Dynamic或者Compact格式的數(shù)據(jù)行結(jié)構(gòu)，其行結(jié)構(gòu)格式如下：|變長字段長度列表（1~2字節(jié)）|NULL標(biāo)志位（1字節(jié)）|記錄頭信息（5字節(jié)）|RowID（6字節(jié)）|事務(wù)ID（6字節(jié)）|回滾指針（7字節(jié)）|row content

1，對(duì)于變長字段，當(dāng)相關(guān)的字段值為NULL時(shí)，相關(guān)字段不會(huì)占用存儲(chǔ)空間。NULL值沒有存儲(chǔ)，不占空間，但是需要一個(gè)標(biāo)志位（一行一個(gè)）。2，對(duì)于變長字段，相關(guān)字段要求NOT NULL，存儲(chǔ)成’’的時(shí)候，也不占用空間，如果一個(gè)表中所有的字典都NOT NULL，行頭不需要NULL的標(biāo)志位3，所有字段都是定長，不管是否要求為NOT NULL，都不需要標(biāo)志位，同時(shí)不需要存儲(chǔ)變長列長度

鑒于null值和非空（not null default ’’）兩種情況，如果一個(gè)字段存儲(chǔ)的內(nèi)容是空，也就是什么都沒有，前者存儲(chǔ)為null，后者存儲(chǔ)為空字符串’’,兩者字段內(nèi)容本身存儲(chǔ)空間大小是一樣的。但是如果一個(gè)表中存儲(chǔ)在可空字段的情況下，其對(duì)應(yīng)的數(shù)據(jù)行的頭部，都需要一個(gè)1字節(jié)的NULL標(biāo)志位，這個(gè)就決定了存儲(chǔ)同樣的數(shù)據(jù)，如果允許為null，相比not null的情況下，每行多了一個(gè)字節(jié)的存儲(chǔ)空間的。這個(gè)因素或者就是某些公司或者個(gè)人堅(jiān)持“所有表禁止null字段”這個(gè)信仰的原因之一（個(gè)人持否定態(tài)度，可以嘗試將數(shù)據(jù)庫中所有的字段都至為not null 然后default一個(gè)值后會(huì)不會(huì)雞飛狗跳）。這里不再去做“微觀”的分析，直接從“宏觀”的角度來看一下差異。

測(cè)試demo

直接創(chuàng)建結(jié)構(gòu)一致，但是一個(gè)表字段not null，一個(gè)表字段為null，然后使用存儲(chǔ)此過程，兩張表同時(shí)按照null值與非null值1:10的比例寫入數(shù)據(jù)，也就是說每10行數(shù)據(jù)中1行數(shù)據(jù)字段為null的方式寫入600W行數(shù)據(jù)。

CREATE TABLE a( id INT AUTO_INCREMENT, c2 VARCHAR(50) NOT NULL DEFAULT ’’, c3 VARCHAR(50) NOT NULL DEFAULT ’’, PRIMARY KEY (id));CREATE TABLE b( id INT AUTO_INCREMENT, c2 VARCHAR(50), c3 VARCHAR(50), PRIMARY KEY (id));CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`( IN `loop_cnt` INT)LANGUAGE SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT ’’BEGIN DECLARE v2 , v3 VARCHAR(36); START TRANSACTION; while loop_cnt>0 do SET v2 = UUID(); SET v3 = UUID(); if (loop_cnt MOD 10) = 0 then INSERT INTO a (c2,c3) VALUES(DEFAULT,DEFAULT); INSERT INTO b (c2,c3) VALUES(DEFAULT,DEFAULT); else INSERT INTO a (c2,c3) VALUES (v2,v3); INSERT INTO b (c2,c3) VALUES (v2,v3); END if ; SET loop_cnt=loop_cnt-1; END while; COMMIT;

a,b兩張表生產(chǎn)完全一致的數(shù)據(jù)。

查看占用的存儲(chǔ)空間情況，從information_schema.TABLES中查詢這兩個(gè)表的存儲(chǔ)信息

1，一個(gè)字節(jié)的差別，體現(xiàn)在avg_row_length,a表因?yàn)樗械淖侄味际莕ot null，因此相比b表，每行節(jié)省了每行節(jié)省了一個(gè)字節(jié)的存儲(chǔ)2，總得空間的差別：a表662683648/1024/1024=631.98437500MB，b表666877952/1024/1024=635.98437500MB，　 也當(dāng)前情況下，600W行數(shù)據(jù)有4MB的差異，差異在1%之內(nèi)，其實(shí)實(shí)際情況下，字段多，table size更大的的時(shí)候，這個(gè)差異會(huì)遠(yuǎn)遠(yuǎn)小于1%。

就存儲(chǔ)空間來說，你跟我說1T的數(shù)據(jù)庫你在乎1GB的存儲(chǔ)空間，隨便一點(diǎn)數(shù)據(jù)/索引碎片空間，一點(diǎn)預(yù)留空間，垃圾文件空間，無用索引空間……，都遠(yuǎn)遠(yuǎn)大于可為空帶來的額外這一點(diǎn)差異。

2，增刪查改的效率

讀寫操作對(duì)比，通過連續(xù)讀寫一個(gè)范圍之內(nèi)的數(shù)據(jù)，來對(duì)比a，b兩張表在讀上面的情況。2.1.）首先buffer pool是遠(yuǎn)大于table size的，因此不用擔(dān)心物理IO引起的差異，目前兩張表的數(shù)據(jù)完全都存在與buffer pool中。2.1.）讀測(cè)試操作放在MySQL實(shí)例機(jī)器上，因此網(wǎng)絡(luò)不穩(wěn)定引起的差異可以忽略。

增刪查改的差異與存儲(chǔ)空間的差異類似，甚至更小，因?yàn)閱涡邢嗖?個(gè)字節(jié)，放大到600W+才能看到一個(gè)5MB級(jí)別的差異，增刪查改的話，各種測(cè)試下來，沒有發(fā)現(xiàn)有明顯的差異

#!/usr/bin/env python3import pymysqlimport timemysql_conn_conf = {’host’: ’127.0.0.1’, ’port’: 3306, ’user’: ’root’, ’password’: ’******’, ’db’: ’db01’}def mysql_read(table_name): conn = pymysql.connect(host=mysql_conn_conf[’host’], port=mysql_conn_conf[’port’], database=mysql_conn_conf[’db’],user=mysql_conn_conf[’user’],password = mysql_conn_conf[’password’]) cursor = conn.cursor() try: cursor.execute(’’’ select id,c2,c3 from {0} where id>3888888 and id<3889999;’’’.format(table_name)) row = cursor.fetchall() except pymysql.Error as e: print('mysql execute error:', e) cursor.close() conn.close()def mysql_write(loop,table_name): conn = pymysql.connect(host=mysql_conn_conf[’host’], port=mysql_conn_conf[’port’], database=mysql_conn_conf[’db’],user=mysql_conn_conf[’user’],password = mysql_conn_conf[’password’]) cursor = conn.cursor() try: if loop%10 == 0: cursor.execute(’’’ insert into {0}} (c2,c3) values(DEFAULT,DEFAULT)’’’.format(table_name)) else: cursor.execute(’’’ insert into {1}} (c2,c3) values(uuid(),uuid())’’’.format(table_name)) except pymysql.Error as e: print('mysql execute error:', e) cursor.close() conn.commit() conn.close()if __name__ == ’__main__’: time_start = time.time() loop=10 while loop>0: mysql_write(loop) loop = loop-1 time_end = time.time() time_c= time_end - time_start print(’time cost’, time_c, ’s’)

3，相關(guān)字段上的語義解析和邏輯考慮

這一點(diǎn)就觀點(diǎn)差異就太多了，也是最容易引起口水或者爭(zhēng)議的了。

1，對(duì)于字符類型，NULL就是不存在，‘’就是空，不存在和空本身就不是一回事，不太認(rèn)同一定要NOT NULL，然后給出默認(rèn)值。2，對(duì)于字符類型，任何數(shù)據(jù)庫中，NULL都是不等于NULL的，因?yàn)樵谔幚硐嚓P(guān)字段上進(jìn)行join或者where篩選的時(shí)候，是不需要考慮連接雙方都為NULL的情況的，一旦用’’替代了NULL，’’是等于’’的，此時(shí)就會(huì)出現(xiàn)與存儲(chǔ)NULL完全不用的語義3，對(duì)于字符類型，一旦將相關(guān)字段default成’’，如何區(qū)分’’與空字符串，比如備注字段，不允許為NULL，default成‘’，那么怎么區(qū)分，NULL表達(dá)的空和默認(rèn)值的空字符串’’4，對(duì)于相關(guān)的查詢操作，如果允許為NULL，篩選非NULL值就是where *** is not null，語義上很清晰直觀，一旦用字段非空，默認(rèn)成’’，會(huì)使用where *** <>’’這種看起來超級(jí)惡心的寫法，究竟要表達(dá)什么，語義上就已經(jīng)開始模糊了5，對(duì)于時(shí)間類型，絕大多數(shù)時(shí)候是不允許有默認(rèn)值的，默認(rèn)多少合適，當(dāng)前時(shí)間合適么，千禧年2000合適么，2008年北京奧運(yùn)會(huì)開幕時(shí)間合適么？6，對(duì)于數(shù)值類型，比如int，比如decimal，在可空的情況下，如果禁止為NULL，默認(rèn)給多少合適，0合適嗎？-1合適嗎？-9999999……合適嗎？10086合適嗎？1024合適嗎？說實(shí)話，默認(rèn)多少都不合適，NULL自身就是最合適的。

個(gè)人觀點(diǎn)很明確，除非有特殊的需求要求一個(gè)字段絕對(duì)不能出現(xiàn)NULL值的情況，正常情況下，該NULL就NULL。如果NULL沒有存在的意義，干脆數(shù)據(jù)庫就不要存在這個(gè)NULL就好了，事實(shí)上，哪個(gè)數(shù)據(jù)庫沒有NULL類型？當(dāng)然也不排除，某些DBA為了顯得自己專業(yè)，弄出來一些莫須有的東西，現(xiàn)在就是有一種風(fēng)氣，在數(shù)據(jù)庫上能提出來的限制條件越多，越有優(yōu)越感。

想起來一個(gè)有關(guān)于默認(rèn)值有意思的事，B站看視頻的時(shí)候某up主曾提到過，因?yàn)锽站把注冊(cè)用戶默認(rèn)為男，出生日期某認(rèn)為某個(gè)指定的日期，導(dǎo)致該up主在對(duì)用戶點(diǎn)為分析后得到一些無法理解的數(shù)據(jù)。

個(gè)人認(rèn)識(shí)有限，數(shù)據(jù)實(shí)話，非常想知道“所有字段非空”會(huì)帶來什么其他哪些正面的影響，以及如何衡量這個(gè)正面的因素，還有，你們真的做到了，可以禁止整個(gè)實(shí)例下所有的庫表中的字段禁止可空（nullable）？

到此這篇關(guān)于MySQL中建表時(shí)可空（NULL）和非空（NOT NULL）的用法詳解的文章就介紹到這了,更多相關(guān)MySQL中建表時(shí)可空和非空 內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！