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

来源:脚本之家  责任编辑:小易  

空(NULL)值表示数值未知。空值不同于空白或零值。没有两个相等的空值。比较两个空值或将空值与任何其它数值相比均返回未知,这是因为每个空值均为未知。在写入数据的时候,空字符串'' 也是一个确定的值,所以就算你定义了 NOT NULL 也可以被写入www.zgxue.com防采集请勿采集本网。

对于MySQL的一些个规范,某些公司建表规范中有一项要求是所有字段非空,意味着没有值的时候存储一个默认值。其实所有字段非空这么说应该是绝对了,应该说是尽可能非空,某些情况下不可能给出一个默认值。

你这样设置之后2113,是不能插入5261的空(NULL),而是能插入空串(\\\\'\\\\')。4102 如果不让插入空串,应该使用1653check,例如:CREATE TABLE table2(title varchar(50)NOT NUL

那么这条要求,是基于哪些考虑因素,存储空间?相关增删查改操作的性能?亦或是其他考虑?该理论到底有没有道理或者可行性,本文就个人的理解,做一个粗浅的分析。

你可以下载一个图文界面的mysql一切就只需要鼠标就行了,我建议你使用Navicat,希望可以解决你的问题

1,基于存储的考虑

mysql有两种方式可以清空表。分别copy为:delete from 表名和truncate table 表名。delete from 表名,删除表数据,全部删除则是可以清空表,相当于一条条删除,需要注意的是,如果有字段是自增

这里对存储的分析要清楚MySQL数据行的存储格式,这里直接从这篇文章白嫖一部分结论,文章里分析的非常清楚(其实也是参考《MySQL技术内容Innodb存储引擎》)。

1 mysql的日期时间类型: date,time,datetime,timestamp;2 示例: drop table t;create table test.t a date, b time, c datetime DEFAULT '2012-01-01', d timestamp NOT NULL DEFAULT CURRENT_

对于默认的Dynamic或者Compact格式的数据行结构,其行结构格式如下:

create table UserInfo(创建 表 表名 这里create table是固定写法,表名自己起 id int parmary key,列名,数据类型。parmary key表示该列为主键列 name varchar(20)not null,列名,数据类型

|变长字段长度列表(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行数据。

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两张表生产完全一致的数据。

查看占用的存储空间情况,从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级别的差异,增删查改的话,各种测试下来,没有发现有明显的差异

#!/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,相关字段上的语义解析和逻辑考虑

这一点就观点差异就太多了,也是最容易引起口水或者争议的了。

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中建表时可空和非空 内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

如果要将数据2113库表的一列置空,首先需要该列5261支持为NULL,因4102此第一步需要检查该项1653,如果该项不允许为空,则需要执行如下调整:alter table tableName modify columnname varchar(2) null;然后再对该列进行置空操作update tableName set columnname =null 执行完成后,columnname 该列内容将为空,update table set column=null,假如这个列没有约束,并且 不是not null的话,就可以了,update table_group set name=NULL where id = 12;本回答被网友采纳,update table_group set name="" where id = 12;内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql创建表的sql语句详细总结
  • mysql元数据如何生成hive建表语句注释脚本详解
  • mysql常用的建表、添加字段、修改字段、添加索引sql语句写法总结
  • 详解在mysql中创建表的教程
  • mysql建表常用sql语句个人经验分享
  • 总结mysql建表、查询优化的一些实用小技巧
  • mysql建表与索引使用规范详解
  • mysql动态创建表,数据分表的存储过程
  • mysql存储过程之case语句用法实例详解
  • mysql通过实例化对象参数查询实例讲解
  • mysql中几种数据统计查询的基本使用教程
  • mysql使用group by分组实现取前n条记录的方法
  • mysql installer web community 5.7.21.0.msi安装图文教程
  • 解决mysql error 1045 (28000)-- access denied for user问题
  • mysql8.0.13免安装版配置教程实例详解
  • mysql中模糊查询的四种用法介绍
  • win10 mysql 5.6.35 winx64免安装版配置教程
  • 数据库mysql性能优化详解
  • MySQL 中怎么把数据库表的一列置空?
  • mysql数据库 nul与null有什么区别
  • MySQL中有唯一性约束的列能否为空
  • 在Mysql中,如何将某一列字段(已经存在)设为唯一、不为空
  • mysql创建表时的空值和非空值设置有什么讲究
  • mysql 对有数据的表添加一个字段默认值为空,如何不影响实时数据的插入
  • mysql如何清空表
  • MySql建表时日期类型的出理
  • java 创建MySQL表
  • mysql多表关联查询字段为空
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql创建表的sql语句详细总结mysql元数据如何生成hive建表语句注释脚本详解mysql常用的建表、添加字段、修改字段、添加索引sql语句写法总结详解在mysql中创建表的教程mysql建表常用sql语句个人经验分享总结mysql建表、查询优化的一些实用小技巧mysql建表与索引使用规范详解mysql动态创建表,数据分表的存储过程mysql存储过程之case语句用法实例详解mysql通过实例化对象参数查询实例讲解mysql中几种数据统计查询的基本使用教程mysql使用group by分组实现取前n条记录的方法mysql installer web community 5.7.21.0.msi安装图文教程解决mysql error 1045 (28000)-- access denied for user问题mysql8.0.13免安装版配置教程实例详解mysql中模糊查询的四种用法介绍win10 mysql 5.6.35 winx64免安装版配置教程数据库mysql性能优化详解mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql update语句的用法详解mysql 的case when 语句使用说明mysql在线ddl gh-ost使用总结centos7上mysql8.0rpm方式安装教程图解在win下mysql备份恢复命令advanced pagination for mysql(mysql高级mysql超长自动截断实例详解重新restore了mysql到另一台机器上后mysqlinux下安装mysql-8.0.20的教程详解mysql查询中limit的大offset导致性能低下mysql安装图解 mysql图文安装教程(详细说mysql服务器登陆故障error 1820 (hy000)的
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved