MySQL中建表时可空 NULL和非空 NOT NULL的用法详解

MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解

本文主要介绍了在MySQL中创建表时，NULL和NOT NULL的用法的详细说明。本文通过示例代码非常详细，对大家的学习或者工作都有一定的参考价值。有需要的朋友就跟着下面的边肖学习吧。

对于一些MySQL规范，在一些公司建表规范中有一个要求，所有字段不能为空，也就是说没有值的时候存储一个默认值。其实所有字段都不是空的，所以应该是绝对的。应该说是尽量不空。在某些情况下，不可能给出默认值。

那么这个要求是基于什么考虑，存储空间？相关的增删改操作性能如何？还是其他考虑？该理论最终是否合理可行，本文就个人理解做粗浅分析。

1，基于存储的考虑

这里，MySQL数据线的存储格式在存储的分析中要明确。在这里，结论的一部分是直接从白嫖的这篇文章中得出的，而且这篇文章中的分析非常清楚(实际上，它也提到了《MySQL技术内容Innodb存储引擎》)。

对于动态或紧凑格式的默认数据行结构，其行结构格式如下：

1.对于可变长度字段，当相关字段值为空时，相关字段不会占用存储空间。空值不被存储，也不占用空间，但是它需要一个标志位(每行一个)。

2.对于可变长度字段，相关字段必须不为空。当存储为“”时，它不占用空间。如果一个表中的所有字典都不为空，那么标题就不需要空标志。

3.所有字段都是定长的，不管是否要求非空，都不需要标志位，也不需要存储变长列。

针对空值和非空(not null默认)两种情况，如果一个字段存储的内容为空，即什么都没有，则前者存储为null，后者存储为空字符串，两个字段的存储空间相同。

但是，如果一个表存储在一个可空的字段中，其对应的数据行的头需要一个1字节的空标志位，它确定存储的是相同的数据。如果允许清空，那么每行比不清空的情况多一个字节的存储空间。

这个因素也是一些公司或个人坚持“所有表都禁止空字段”这一信念的原因之一(个人持否定态度，可以尝试将数据库中的所有字段都设为非空然后默认一个值，这样会鸡飞狗跳)。

与其在这里做“微观”分析，不如直接从“宏观”的角度来看区别。

测试演示

直接创建结构一致，但是一个表字段不为空，另一个表字段为空。然后利用存储过程，两个表按照空值和非空值1:10的比例同时写入数据，也就是600W行数据以每10行数据中有一行数据字段为空的方式写入。

创建表a

（

id INT自动增量，

c2 VARCHAR(50) NOT NULL默认值，

c3 VARCHAR(50) NOT NULL默认值，

主键(id)

);

创建表b

（

id INT自动增量，

c2 VARCHAR(50)，

c3 VARCHAR(50)，

主键(id)

);

CREATE DEFINER=`root`@`% `过程` create_test_data `(

IN `loop_cnt` INT

)

语言SQL

不确定

包含SQL

SQL安全定义器

开始

声明v2，v3 VARCHAR(36)；

开始交易；

而loop_cnt0会

SET v2=UUID()；

SET v3=UUID()；

如果(循环计数模10)=0，则

插入一个(c2，c3)值(DEFAULT，默认值)；

插入b (c2，c3)值(默认，缺省)；

其他

插入一个(c2，c3)值(v2，v3)；

插入b (c2，c3)值(v2，v3)；

结束if；

设置loop _ CNT=loop _ CNT-1；

结束while

提交；

两个表A和B产生相同的数据。

检查占用的存储空间，从information_schema查询这两个表的存储信息。桌子

1，一个字节的差异反映在AVG _行_长度中。与表B相比，表A每行节省一个字节的存储空间，因为所有字段都不为空。

2.总空间的差异：表A 662683648/1024/1024=631.98437500 MB，表B 66877952/1024/1024=635.847500 MB，

目前600W行数据有4MB的差异，差异在1%以内。事实上，当字段较多，表的大小较大时，差别会远远小于1%。

就存储空间而言，您告诉我您关心1T数据库的1GB存储空间。任何数据/索引碎片空间、一点保留空间、垃圾文件空间、无用的索引空间.远远大于可能导致无效的额外差异。

2，增删查改的效率

读写操作比较，通过在一个范围内连续读写数据，来比较A表和B表的读取情况。

2.1.)首先，缓冲池远大于表大小，不用担心物理IO带来的差异。目前，这两个表的数据都存在缓冲池中。

2.1.)读测试操作放在MySQL实例机上，网络不稳定带来的差异可以忽略。

增删改的差别和存储空间的差别差不多，甚至更小，因为单行有1个字节的差别。放大到600W W只能看到5MB级别的差别，如果添加、删除、修正，各种测试后都发现没有明显差别。

#!/usr/bin/env python3

导入pymysql

导入时间

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()

尝试：

cursor.execute( select id，c2，C3 from { 0 } where id 3888888 and id 3889999；.格式(表名))

row=cursor.fetchall()

除了pymysql。错误为e:

print(mysql执行错误：，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()

尝试：

如果loop==0:

cursor . execute( insert into { 0 } }(C2，c3)值(DEFAULT，DEFAULT) 。格式(表名))

否则：

cursor . execute( insert into { 1 } }(C2，c3)值(uuid()，uuid()) 。格式(表名))

除了pymysql。错误为e:

print(mysql执行错误：，e)

cursor.close()

conn.commit()

conn.close()

if __name__==__main__ :