Summary 总结
null value always leads to many uncertainties when disposing sql statement.It may cause bad performance accidentally.
列中使用NULL值容易引发不受控制的事情发生,有时候还会严重托慢系统的性能.
- null value will not be estimated in aggregate function() which may cause inaccurate results.
对含有NULL值的列进行统计计算,eg. count(),max(),min(),结果并不符合我们的期望值. - null value will influence the behavior of the operations such as “distinct”,“group by”,“order by” which causes wrong sort.
干扰排序，分组,去重结果. - null value needs ifnull() function to do judgement which makes the program code more complex.
有的时候为了消除NULL带来的技术债务,我们需要在SQL中使用IFNULL()来确保结果可控,但是这使程序变得复杂. - null value needs a extra 1 byte to store the null information in the rows.
NULL值并是占用原有的字段空间存储,而是额外申请一个字节去标注,这个字段添加了NULL约束.(就像额外的标志位一样)
As these above drawbacks,it’s not recommended to define columns with default null.
We recommand to define “not null” on all columns and use zero number & vacant string to substitute relevant data type of null.
根据以上缺点,我们并不推荐在列中设置NULL作为列的默认值,你可以使用NOT NULL消除默认设置,使用0或者''空字符串来代替NULL.
MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解
更新时间：2020年07月27日 17:18:16 作者：MSSQL123
这篇文章主要介绍了MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
对于MySQL的一些个规范，某些公司建表规范中有一项要求是所有字段非空，意味着没有值的时候存储一个默认值。其实所有字段非空这么说应该是绝对了，应该说是尽可能非空，某些情况下不可能给出一个默认值。
那么这条要求，是基于哪些考虑因素，存储空间？相关增删查改操作的性能？亦或是其他考虑？该理论到底有没有道理或者可行性，本文就个人的理解，做一个粗浅的分析。
1，基于存储的考虑
这里对存储的分析要清楚MySQL数据行的存储格式，这里直接从这篇文章白嫖一部分结论，文章里分析的非常清楚（其实也是参考《MySQL技术内容Innodb存储引擎》）。
对于默认的Dynamic或者Compact格式的数据行结构，其行结构格式如下：
|变长字段长度列表（1~2字节）|NULL标志位（1字节）|记录头信息（5字节）|RowID（6字节）|事务ID（6字节）|回滚指针（7字节）|row content
1，对于变长字段，当相关的字段值为NULL时，相关字段不会占用存储空间。NULL值没有存储，不占空间，但是需要一个标志位（一行一个）。
2，对于变长字段，相关字段要求NOT NULL，存储成''的时候，也不占用空间，如果一个表中所有的字典都NOT NULL，行头不需要NULL的标志位
3，所有字段都是定长，不管是否要求为NOT NULL，都不需要标志位，同时不需要存储变长列长度
鉴于null值和非空（not null default ''）两种情况，如果一个字段存储的内容是空，也就是什么都没有，前者存储为null，后者存储为空字符串'',两者字段内容本身存储空间大小是一样的。
但是如果一个表中存储在可空字段的情况下，其对应的数据行的头部，都需要一个1字节的NULL标志位，这个就决定了存储同样的数据，如果允许为null，相比not null的情况下，每行多了一个字节的存储空间的。
这个因素或者就是某些公司或者个人坚持“所有表禁止null字段”这个信仰的原因之一（个人持否定态度，可以尝试将数据库中所有的字段都至为not null 然后default一个值后会不会鸡飞狗跳）。
这里不再去做“微观”的分析，直接从“宏观”的角度来看一下差异。
测试demo
直接创建结构一致，但是一个表字段not null，一个表字段为null，然后使用存储此过程，两张表同时按照null值与非null值1:10的比例写入数据，也就是说每10行数据中1行数据字段为null的方式写入600W行数据。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | 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 SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
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两张表生产完全一致的数据。
查看占用的存储空间情况，从information_schema.TABLES中查询这两个表的存储信息
1，一个字节的差别，体现在avg_row_length,a表因为所有的字段都是not null，因此相比b表，每行节省了每行节省了一个字节的存储
2，总得空间的差别：a表662683648/1024/1024=631.98437500MB，b表666877952/1024/1024=635.98437500MB，
也当前情况下，600W行数据有4MB的差异，差异在1%之内，其实实际情况下，字段多，table size更大的的时候，这个差异会远远小于1%。
就存储空间来说，你跟我说1T的数据库你在乎1GB的存储空间，随便一点数据/索引碎片空间，一点预留空间，垃圾文件空间，无用索引空间……，都远远大于可为空带来的额外这一点差异。
2，增删查改的效率
读写操作对比，通过连续读写一个范围之内的数据，来对比a，b两张表在读上面的情况。
2.1.）首先buffer pool是远大于table size的，因此不用担心物理IO引起的差异，目前两张表的数据完全都存在与buffer pool中。
2.1.）读测试操作放在MySQL实例机器上，因此网络不稳定引起的差异可以忽略。
增删查改的差异与存储空间的差异类似，甚至更小，因为单行相差1个字节，放大到600W+才能看到一个5MB级别的差异，增删查改的话，各种测试下来，没有发现有明显的差异
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | #!/usr/bin/env python3
import pymysql
import time
mysql_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，相关字段上的语义解析和逻辑考虑
这一点就观点差异就太多了，也是最容易引起口水或者争议的了。
1，对于字符类型，NULL就是不存在，‘'就是空，不存在和空本身就不是一回事，不太认同一定要NOT NULL，然后给出默认值。
2，对于字符类型，任何数据库中，NULL都是不等于NULL的，因为在处理相关字段上进行join或者where筛选的时候，是不需要考虑连接双方都为NULL的情况的，一旦用''替代了NULL，''是等于''的，此时就会出现与存储NULL完全不用的语义
3，对于字符类型，一旦将相关字段default成''，如何区分''与空字符串，比如备注字段，不允许为NULL，default成‘'，那么怎么区分，NULL表达的空和默认值的空字符串''
4，对于相关的查询操作，如果允许为NULL，筛选非NULL值就是where *** is not null，语义上很清晰直观，一旦用字段非空，默认成''，会使用where *** <>''这种看起来超级恶心的写法，究竟要表达什么，语义上就已经开始模糊了
5，对于时间类型，绝大多数时候是不允许有默认值的，默认多少合适，当前时间合适么，千禧年2000合适么，2008年北京奥运会开幕时间合适么？
6，对于数值类型，比如int，比如decimal，在可空的情况下，如果禁止为NULL，默认给多少合适，0合适吗？-1合适吗？-9999999……合适吗？10086合适吗？1024合适吗？说实话，默认多少都不合适，NULL自身就是最合适的。
个人观点很明确，除非有特殊的需求要求一个字段绝对不能出现NULL值的情况，正常情况下，该NULL就NULL。
如果NULL没有存在的意义，干脆数据库就不要存在这个NULL就好了，事实上，哪个数据库没有NULL类型？
当然也不排除，某些DBA为了显得自己专业，弄出来一些莫须有的东西，现在就是有一种风气，在数据库上能提出来的限制条件越多，越有优越感。
想起来一个有关于默认值有意思的事，B站看视频的时候某up主曾提到过，因为B站把注册用户默认为男，出生日期某认为某个指定的日期，导致该up主在对用户点为分析后得到一些无法理解的数据。
个人认识有限，数据实话，非常想知道“所有字段非空”会带来什么其他哪些正面的影响，以及如何衡量这个正面的因素，还有，你们真的做到了，可以禁止整个实例下所有的库表中的字段禁止可空（nullable）？
MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解MySQL不推荐列默认值设置为null到底是为什么参考资料：https://blog.csdn.net/qq_30549099/article/details/107395521https://www.jb51.net/article/191848.htm必须把字段定义为NOT NULL并且提供默认值解读：null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 null 这种...
select * from table where column is not null
带着上面几个疑问，我们来简单的研究一下null 和 not null 到底有什么不一样，他们之间的区别是什么以及各自的效率问题。
首先，我们要搞清楚“空值” 和 “NULL” 的概念：
1、空值是不占用空间的
2、mysql中的NU
今天用到了MySql里的isnull才发现他和MSSQL里的还是有点区别，现在简单总结一下：
mysql中isnull,ifnull,nullif的用法如下：
isnull(expr) 的用法：
如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。
mysql> select isnull(1+1);
mysql> select isnull(1/0);
使用= 的null 值对比通常是错误的。
isnull() 函数同 is null比较操作符具有一些相同的特性。请参见有关is null 的说明。
IFNULL(expr1,expr2)
当在创建表的时候，有声明哪个字段是NOT NULL，那么在插入值的时候，该字段的所在位置的值就不能为空，反之可以写null。如果创建表的时候声明了某个字段为NOT NULL ，而在插入值的时候在该字段的位置写null，系统就会报错。
具体命令行案例如下图：
在创建表的时候尽量把字段的默认值设置成 not null，除非你想存储null；因为在mysql中为null的的字段不会走索引，做统计的时候也不会被统计进去，如果想统计进去必须做特定的处理，这样做比较复杂。可以给字段的值设置成0、一个特殊的值或者一个空串代替空值。
MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。
可空列需要更多的储存空间，还需要在MySQL内部进行特殊处...
相信很多用了MySQL很久的人，对这两个字段属性的概念还不是很清楚，一般会有以下疑问：
我字段类型是not null，为什么我可以插入空值
为毛not null的效率比null高
判断字段不为空的时候，到底要 select * from table where column <> ” 还是要用 select * from table wherecolumn is not null 呢。
带着上面几个疑问，我们来深入研究一下null 和 not null 到底有什么不一样。
首先，我们要搞清楚“空值” 和 “NULL” 的概念：
空值是不占用空间的
mysql中的NULL其实
mysql不能像oracle一样，使用nulls first 和nulls last，可以使用下面的语句代替
nulls first
order by IF(ISNULL(my_field),0,1), my_field;
nulls last
order by IF(ISNULL(my_field),1,0),my_field;
1. 占用空间区别：空值不占用任何存储空间，而空字符串占用一个字节的存储空间。
2. 插入/查询方式区别：插入空值时，可以直接将字段设置为null，而插入空字符串时，需要将字段设置为''。查询时，可以使用is null/is not null查询空值，而空字符串可以使用=或者!=、<、>等算术运算符进行查询。
3. COUNT和IFNULL函数：在使用COUNT函数统计某个字段的记录数量时，空值不会被计入，而空字符串会被计算在内。另外，IFNULL函数可以用于将空值替换为指定的值，但对空字符串无效。
综上所述，空值和空字符串在存储空间、插入/查询方式以及COUNT和IFNULL函数的处理上存在区别。<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
- *1* *2* *3* [MySQL数据库中空值（null）和空字符串（‘‘）的区别，你不会不知道吧！](https://blog.csdn.net/weixin_46460843/article/details/118652015)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"]
[ .reference_list ]
