MySQL中的唯一性约束与NULL详解

前言

之前做的一个需求,简化描述下就是接受其他组的 MQ 的消息,然后在数据库里插入一条记录。为了防止他们重复发消息,插入多条重复记录,所以在表中的几个列上加了个唯一性索引。

CREATE UNIQUE INDEX IDX_UN_LOAN_PLAN_APP ON testTable (A, B, C);

这时 A,B,C 三列都是不允许 NULL 值的,唯一性约束也是 work 的。

后来由于需求的变化,修改了以前的唯一性约束,又多加了一列。(至于为什么加就不赘述了)。

ALTER TABLE testTable
DROP INDEX IDX_UN_LOAN_PLAN_APP,
ADD UNIQUE KEY `IDX_UN_LOAN_PLAN_APP` (A, B, C, D);

新加的 D 是类型是 datetime, 允许为 NULL,默认值为 NULL。之所以默认值为 NULL,是考虑到不是所有记录都有这个时间的, 如果强行设置一个 Magic Value (比如'1970-01-01 08:00:00‘)当做默认值,看起来很奇怪。

蓝后。。。就出问题了。加了 D 之后,唯一性约束基本就失效了。

Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK
Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK
Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK

上面的三条 SQL 都是可以执行成功的,数据库中会有多条一样的记录。可按照我们以前的构想,在执行后两条 SQL 时 应该抛出 ‘Duplicate key' 的异常的。

后来查了一下,才发现其实 MySQL 官方文档上已经明确说了这一点, 唯一性索引是允许多个 NULL 值的存在的:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

从下表中也可以看出来不管是采用什么类型的存储引擎,在建立 unique key 的时候都是允许多个 NULL 存在的。。。。

细想想,其实也蛮合理,毕竟在 MySQL 中认为 NULL 代表着“未知”。 在 SQL 中,任何值与 NULL 的比较返回值都是 NULL 而不是 TRUE, 就算 NULL 与 NULL 的比较也是返回 NULL。

所以只能 fix 了。。。解决办法也蛮简单粗暴的,直接把线上数据刷了一遍,将“1970-01-01 08:00:00”作为默认值,然后把那列改为不允许为 NULL 的了,咳咳。

MySQL 官网上也有蛮多人讨论过这个问题,一部分人认为这是 MySQL 的 bug, 另一部分则认为是一个 feature,附上链接。

MySQL Bugs: #8173: unique index allows duplicates with null values

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

(0)

相关推荐

  • MySQL中的唯一性约束与NULL详解

    前言 之前做的一个需求,简化描述下就是接受其他组的 MQ 的消息,然后在数据库里插入一条记录.为了防止他们重复发消息,插入多条重复记录,所以在表中的几个列上加了个唯一性索引. CREATE UNIQUE INDEX IDX_UN_LOAN_PLAN_APP ON testTable (A, B, C); 这时 A,B,C 三列都是不允许 NULL 值的,唯一性约束也是 work 的. 后来由于需求的变化,修改了以前的唯一性约束,又多加了一列.(至于为什么加就不赘述了). ALTER TABLE

  • MySQL中replace into与replace区别详解

    目录 0.故事的背景 1.replace into 的使用方法 2.有唯一索引时—replace into & 与replace 效果 3.没有唯一索引时—replace into 与 replace 1).replace函数的具体情况 2).replace into 函数的具体情况 4.replace的用法 本篇为抛砖引玉篇,之前没关注过replace into 与replace 的区别.经过多个场景测试,居然没找到在插入数据的时候两者有什么本质的区别?如果了解详情的伙伴们,请告知留言告知一二

  • mysql中decimal数据类型小数位填充问题详解

    前言 在开发过程中,我们往往会用到decimal数据类型.因为decimal是MySQL中存在的精准数据类型. MySQL中的数据类型有:float,double等非精准数据类型和decimal这种精准. 区别:float,double等非精准类型,在DB中保存的是近似值. Decimal则以字符串的形式保存精确的原始数值. decimal介绍: decimal(a,b) 其中:a指定指定小数点左边和右边可以存储的十进制数字的最大个数,最大精度38.b指定小数点右边可以存储的十进制数字的最大个数

  • MySQL中的行级锁定示例详解

    前言 锁是在执行多线程时用于强行限定资源访问的同步机制,数据库锁根据锁的粒度可分为行级锁,表级锁和页级锁 行级锁 行级锁是mysql中粒度最细的一种锁机制,表示只对当前所操作的行进行加锁,行级锁发生冲突的概率很低,其粒度最小,但是加锁的代价最大.行级锁分为共享锁和排他锁. 特点: 开销大,加锁慢,会出现死锁:锁定粒度最小,发生锁冲突的概率最大,并发性也高: 实现原理: InnoDB行锁是通过给索引项加锁来实现的,这一点mysql和oracle不同,后者是通过在数据库中对相应的数据行加锁来实现的,

  • Mysql中事务ACID的实现原理详解

    引言 照例,我们先来一个场景~ 面试官:"知道事务的四大特性么?" 你:"懂,ACID嘛,原子性(Atomicity).一致性(Consistency).隔离性(Isolation).持久性(Durability)!" 面试官:"你们是用mysql数据库吧,能简单说说innodb中怎么实现这四大特性的么?" 你:"我只知道隔离性是怎么做的balabala~~" 面试官:"还是回去等通知吧~" OK,回到正题

  • 关于Mysql中ON与Where区别问题详解

    今天工作的时候编写了一个SQL,大家一起来看一下 SELECT * FROM user user LEFT JOIN user_message msg ON user.id = msg.user_id AND user.name = '张三' 我们有一张user表 我们还有一张user_message表 我们执行上面的SQL后,查询结果如下 并不如我们所想的,只出现张三的结果,这是为什么呢? 经过我的整理发现查询是如下原因 关联的时候ON条件是在合并两张表形成临时表前进行的条件筛选,所以在使用

  • django连接Mysql中已有数据库的方法详解

    我的Mysql中已经有了项目需要使用的相关数据库,现在需要通过django来获取Mysql里的数据并使用,下面记录配置django与mysql的连接过程. (此处默认您已经建好了django框架,如果没有准备好django,请参照我本篇文章 https://www.jb51.net/article/249861.htm 首先需要在项目的settings.py文件中配置mysql的连接信息:把之前配置的sqlite配置信息替换.之前默认的数据库配置信息如图,是与sqlite连接的 现在我们需要替换

  • mysql中关键词exists的用法实例详解

    目录 前言 语法解释 语法 说明 使用案例 环境准备 常用查询 exists与in的效率比较 循环嵌套查询执行原理 循环优化策略 exists和in查询原理的区别 结论 总结 前言 在日常开发中,用mysql进行查询的时候,有一个比较少见的关键词exists,我们今天来学习了解一下这个exists这个sql关键词的用法,这样在工作中遇到一些特定的业务场景就可以有更加多样化的解决方案 语法解释 语法 SELECT column1 FROM t1 WHERE [conditions] and EXI

  • Mysql中的concat函数(拼接函数)详解

    目录 Mysql中的concat函数(拼接函数) 概述 Mysql Concat函数 Mysql Concat_Ws函数 Mysql中的concat函数(拼接函数) 概述 很多时候,我们需要将不同地方获得的字符串拼接在一起,此时就需要使用CONCAT和CONCAT_WS函数 要连接两个或多个引用的字符串值,请将字符串放在一起,如下所示: mysql> SELECT 'MySQL ' 'String ' 'Concatenation'; +----------------------------+

  • MySQL中replace into语句的用法详解

    在向表中插入数据的时候,经常遇到这样的情况: 1.首先判断数据是否存在: 2.如果不存在,则插入: 3.如果存在,则更新.   在 SQL Server 中可以这样写: 复制代码 代码如下: if not exists (select 1 from table where id = 1) insert into table(id, update_time) values(1, getdate()) else update table set update_time = getdate() whe

随机推荐