mysql 字段定义不要用null的原因分析

一 NULL 为什么这么经常用

(1) java的null

null是一个让人头疼的问题,比如java中的NullPointerException。为了避免猝不及防的空指针,需要小心翼翼地各种if判断,麻烦又臃肿.

为此有很多的开源包都有诸多处理

common lang3的StringUtils.isBlank(); CollectionUtils.isEmpty();

guava的Optional

甚至java8也引入了Optional来避免这一问题(和guava的大同小异,用法稍有一点点变化)

(2) mysql的null为什么横行滥用

(a) 创建不规范 null是创建数据表时候默认的,一些mysql客户端的自动生成表语句里面可能也没有not null的指定。

(b) 错误认识 会有人觉得not null需要更多的空间

(c) 图省事 null在开发中不用判断插入数据,写sql更方便

二 官方文档

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难以优化引用可空列查询,它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间,还需要mysql内部进行特殊处理。可空列被索引后,每条记录都需要一个额外的字节,还能导致MYisam 中固定大小的索引变成可变大小的索引。 —— 出自《高性能mysql第二版》

如此看来,不指定not null并没有性能上的优势。

三 mysql不用null的理由

(1)所有使用NULL值的情况,都可以通过一个有意义的值的表示,这样有利于代码的可读性和可维护性,并能从约束上增强业务数据的规范性。

(2)NULL值到非NULL的更新无法做到原地更新,更容易发生索引分裂,从而影响性能。(null -> not null性能提升很小,除非确定它带来了问题,否则不要当成优先的优化措施)

(3)NULL值在timestamp类型下容易出问题,特别是没有启用参数explicit_defaults_for_timestamp

(4)NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果,查询容易出错

四 null引发的bad case

数据初始化:

create table table1 (
    `id` INT (11) NOT NULL,
    `name` varchar(20) NOT NULL
)

create table table2 (
    `id` INT (11) NOT NULL,
    `name`  varchar(20)
)

insert into table1 values (4,"zhaoyun"),(2,"zhangfei"),(3,"liubei")
insert into table2 values (1,"zhaoyun"),(2, null)

(1)NOT IN子查询在有NULL值的情况下返回永远为空结果,查询容易出错

select name from table1 where name not in (select name from table2 where id!=1)

+-------------+
|      name   |
|-------------|
+-------------+

(2) 列值允许为空,索引不存储null值,结果集中不会包含这些记录。

select * from table2 where name != 'zhaoyun'

+------+-------------+
|   id |      name   |
|------+-------------|
|      |             |
+------+-------------+

select * from table2 where name != 'zhaoyun1'

+------+-------------+
|   id |      name   |
|------+-------------|
|   1  |  zhaoyun    |
+------+-------------+

(3) 使用concat拼接时,首先要对各个字段进行非null判断,否则只要任何一个字段为空都会造成拼接的结果为null

select concat("1", null) from dual;

+--------------------+
|   concat("1", null)|
|--------------------|
|               NULL |
+--------------------+

(4) 当计算count时候null column不会计入统计

select count(name) from table2;

+--------------------+
|   count(user_name) |
|--------------------|
|                  1 |
+--------------------+

五 索引长度对比

alter table table1 add index idx_name (name);
alter table table2 add index idx_name (name);
explain select * from table1 where name='zhaoyun';
explain select * from table2 where name='zhaoyun';

table1的key_len = 82

table2的key_len = 83

key_len 的计算规则和三个因素有关:数据类型、字符编码、是否为 NULL

key_len 82 = 20 * 4(utf8mb4 - 4字节, utf8 - 3字节) + 2(存储varchar变长字符长度为2字节,定长字段无需额外的字节)

key_len 83 = 20 * 4(utf8mb4 - 4字节, utf8 - 3字节) + 2(存储varchar变长字符长度为2字节,定长字段无需额外的字节) + 1(是否为null的标志)

所以说索引字段最好不要为NULL,因为NULL会使索引、索引统计和值更加复杂,并且需要额外一个字节的存储空间。

到此这篇关于mysql 字段定义不要用null的分析的文章就介绍到这了,更多相关mysql 字段定义null内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL中可为空的字段设置为NULL还是NOT NULL

    经常用mysql的人可能会遇到下面几种情况: 1.我字段类型是not null,为什么我可以插入空值 2.为什么not null的效率比null高 3.判断字段不为空的时候,到底要用 select * from table where column <> '' 还是要用 select * from table where column is not null 带着上面几个疑问,我们来简单的研究一下null 和 not null 到底有什么不一样,他们之间的区别是什么以及各自的效率问题. 首先,

  • mysql 求解求2个或以上字段为NULL的记录

    核心代码 /*-------------------------------- 求2个或以上字段为NULL 的记录 t1: id, id1, id2, id3, id4, id5, id6 在t1 表中有个字段: 其中id是主键: 怎样打印其中个字段或以上为NULL 的记录id? 另外,存储过程中怎么实现按顺序一条一条读取记录最方便? 注:主键id 是没有顺序的,也可能是字符串的: -----------------------------------------*/ drop table if

  • MySQL查询空字段或非空字段(is null和not null)

    现在我们先来把test表中的一条记录的birth字段设置为空. mysql> update test set t_birth=null where t_id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 OK,执行成功! 设置一个字段值为空时的语法为:set <字段名>=NULL 说明一下,这里没有大小写的区分,可以是null,也可以是NULL. 下面看看结果: mysql&

  • 为什么mysql字段要使用NOT NULL

    最近刚入职新公司,发现数据库设计有点小问题,数据库字段很多没有NOT NULL,对于强迫症晚期患者来说,简直难以忍受,因此有了这篇文章. 基于目前大部分的开发现状来说,我们都会把字段全部设置成NOT NULL并且给默认值的形式. 通常,对于默认值一般这样设置: 整形,我们一般使用0作为默认值. 字符串,默认空字符串 时间,可以默认1970-01-01 08:00:01,或者默认0000-00-00 00:00:00,但是连接参数要添加zeroDateTimeBehavior=convertToN

  • mysql 字段定义不要用null的原因分析

    一 NULL 为什么这么经常用 (1) java的null null是一个让人头疼的问题,比如java中的NullPointerException.为了避免猝不及防的空指针,需要小心翼翼地各种if判断,麻烦又臃肿. 为此有很多的开源包都有诸多处理 common lang3的StringUtils.isBlank(); CollectionUtils.isEmpty(); guava的Optional 甚至java8也引入了Optional来避免这一问题(和guava的大同小异,用法稍有一点点变化

  • @Autowired注入为null问题原因分析

    问题说明 最近看到Spring事务,在学习过程中遇到一个很苦恼问题 搭建好Spring的启动环境后出现了一点小问题 在启动时候却出现[java.lang.NullPointerException] 不过因为当时一个小小的疏忽很low的问题 请往下看... 工程结构 代码片段 spring.xml <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springfr

  • MySQL字段为 NULL的5大坑

    目录 1.count 数据丢失 2.distinct 数据丢失 3.select 数据丢失 4.导致空指针异常 5.增加了查询难度 扩展知识:NULL 不会影响索引 总结 正式开始之前,我们先来看下 MySQL 服务器的配置和版本号信息,如下图所示: “兵马未动粮草先行”,看完了相关的配置之后,我们先来创建一张测试表和一些测试数据. -- 如果存在 person 表先删除 DROP TABLE IF EXISTS person; -- 创建 person 表,其中 username 字段可为空,

  • mysql字段为NULL索引是否会失效实例详解

    项目场景: 很多博客说mysql在字段中创建普通索引,如果该索引中的数据存在null值是不走索引这个结论是错误的,不过尽量还是设置默认值.(版本8.0低于这个版本可能结果不一致) 1.创建表sc_base_color,其中普通索引为 “name,group_num”,这里暂时不测组合索引,下面再测试. CREATE TABLE `sc_base_color` ( `id` bigint NOT NULL AUTO_INCREMENT, `group_num` bigint DEFAULT NUL

  • Mysql字段为null的加减乘除运算方式

    目录 Mysql字段为null的加减乘除运算 解决办法 数据库关于null不参与运算的坑 举个例子:(阅读时:请记住这个字段以及它的值 ) 发生场景 Mysql字段为null的加减乘除运算 数据库表test_table 如下查询: select id, total, used, (total - used) as have from test_table; 查询结果: 解决办法 使用IFNULL 函数来解决NULL值问题 select id, IFNULL(total,0) as total,

  • MySQL数据定义语言DDL的基础语句

    MySQL DDL 语句 什么是DDL,DML. DDL 是数据定义语言,就是对数据库,表层面的操作,如 CREATE,ALTER,DROP.DML 是数据操作语言,也就是对表中数据的增删改查,如 SELECT,UPDATE,INSERT,DELETE. 假设现在有数据库 lian_xi 里面有表 user 和 orders; 1.1登陆MySQL环境 在cmd窗口中输入mysql -u root -p登录MySQL环境 1.2查看数据库 Show databases; 1.3使用数据库 Use

  • 区分MySQL中的空值(null)和空字符('')

    日常开发中,一般都会涉及到数据库增删改查,那么不可避免会遇到Mysql中的NULL和空字符. 空字符('')和空值(null)表面上看都是空,其实存在一些差异: 定义: 空值(NULL)的长度是NULL,不确定占用了多少存储空间,但是占用存储空间的 空字符串('')的长度是0,是不占用空间的 通俗的讲: 空字符串('')就像是一个真空转态杯子,什么都没有. 空值(NULL)就像是一个装满空气的杯子,含有东西. 二者虽然看起来都是空的.透明的,但是有着本质的区别. 区别: 在进行count()统计

  • MySQL limit分页大偏移量慢的原因及优化方案

    在 MySQL 中通常我们使用 limit 来完成页面上的分页功能,但是当数据量达到一个很大的值之后,越往后翻页,接口的响应速度就越慢. 本文主要讨论 limit 分页大偏移量慢的原因及优化方案,为了模拟这种情况,下面首先介绍表结构和执行的 SQL. 场景模拟 建表语句 user 表的结构比较简单,id.sex 和 name,为了让 SQL 的执行时间变化更加明显,这里有9个姓名列. CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREME

  • MySql范围查找时索引不生效问题的原因分析

    1 问题描述 本文对建立好的复合索引进行排序,并取记录中非索引字段,发现索引不生效,例如,有如下表,DDL语句为: CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_da

随机推荐