mysql的null值坑（UNIQUE KEY失效和判断条件失效）

最新推荐文章于 2025-06-10 23:53:26 发布
最新推荐文章于 2025-06-10 23:53:26 发布
阅读量4.5k 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/elfindemon/article/details/81660737

一个mysql菜鸟的经历。mysql 踩的坑。

1. 值为空 导致 UNIQUE KEY失效

数据存放在mysql，想通过UNIQUE KEY 和INSERT ON DUPLICATE KEY UPDATE 来清洗重复数据，结果发现插入成功后数据有重复。

发现原因是 UNIQUE KEY里有NULL值，当UNIQUE KEY 里有NULL值后，mysql不会监测UNIQUE KEY 认为有重复。

修复方法是给 各 UNIQUE KEY设定默认值，例如 if(house_type_id,0) as house_type_id。

测试验证插入数据不会有重复了。

2. 值为空 导致 判断条件失效

select if(null in (1),1,0) ,if(not null in (1),1,0) -- 结果是0,0

空值的所有 比较和集合 判断逻辑都是 null (而不是false)，所以加not 也是null（not null=null !）

 

MySql联合唯一索引含null值，索引失效
ithongchou的博客
08-14 1129
联合唯一索引在处理包含NULL值的列时可能会出现问题，主要是因为NULL被视为未知且不等于其他NULL值。了解这些行为可以帮助你更好地设计数据库架构和编写查询。
MySql索引失效-联合唯一索引含null值
森伯416的博客
07-13 7421
一.背景 今天在测试过程中发现有数据重复的现象，想设计一个唯一索引达到幂等的效果，但是发现设置了唯一索引以后还是可以插入相同的数据，经过排查，发现联合唯一索引中含有null值会导致索引失效。 二.问题复现 现在我们来复现一下这个场景。 2.1 首先创建一个 user 表，指定 user_name + address 为联合唯一索引 CREATE TABLE `t_user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
【MySQL】可重复读模式下 unique key失效案例
weixin_33835103的博客
04-29 185
一【背景】 今天上午文能提笔安天下,武能上马定乾坤的登博给团队出了一道题目，谁先复现问题，奖励星巴克一杯。激起了一群忙碌的屌丝DBA的极大热情。问题是这样滴，如下图登博提示了几个细节： 1. code上的uk并未失效。 2. rr隔离级别。 3. 有并发线程的操作。二 【原理分析】1 事务隔离级别的...
mysql中的unique key和null之间的关系
qq_41030039的博客
08-19 7020
在mysql 中，唯一约束unique key是为了防止数据重复而设立的。一个表可以有多个唯一约束，但只能有一个主键。 神奇的是，唯一约束竟然可以插入null值，而且可以插入多个null值，这一点令我难以理解。所以查阅了一下，发现解释很多种，但比较信服的是这个说法： 主键和唯一键约束是通过参考索引实施的，如果插入的值均为NULL，则根据索引的原理，全NULL值不被记录在索引上，所以插入全NULL值...
主键（PRIMARY KEY）与唯一键（UNIQUE KEY）的区别详解
最新发布
Mylvzi的博客
06-10 1034
❌ 报错：只能定义一个主键！Q1：主键和唯一键的区别？主键用于唯一标识一行数据，不能为 NULL，一个表只能有一个主键；唯一键也约束唯一性，但可以为 NULL，且一个表可以有多个唯一键；主键是强约束（唯一 + 非空 + 唯一标识），唯一键是弱约束（只是唯一）。Q2：可以同时设置主键和唯一键吗？答：可以。一个字段作为主键，另一个或多个字段作为唯一键，不冲突。Q3：主键和唯一键哪个更适合身份证号字段？如果身份证号一定不能为空并作为唯一身份标识，建议用主键；
mysql unique null_UNIQUE KEY可以为NULL，但是NULL只能有一个，为什么我的结果和这个不一样...
weixin_39902184的博客
01-19 1250
我创建了一个表t3| t3 | CREATE TABLE `t3` (`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,`username` varchar(20) DEFAULT NULL,`test` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `username` (`us...
mysql unique不起作用_MySQL UNIQUE键不起作用
weixin_35801556的博客
01-26 853
我在库存表上建立类型,通过6个不同因素跟踪库存.我使用的查询与此类似：INSERT INTO inventory ( productid, factor1, factor2, factor3, factor4, factor5, factor6, quantity, serial_number)VALUES (242332,1,1,1,'V67',3.30,'NEW',10,NULL)ON DUP...
mysql unique key 作用,MySQL UNIQUE键不起作用
weixin_39617669的博客
01-19 357
I am building as type on inventory table that keeps track of stock by 6 different factors. I am using an I query much like this one:INSERT INTO inventory ( productid, factor1, factor2, factor3, factor...
MySQL高频基本面试问题整理
热门推荐
张彦峰的博客
04-07 174万+
总结大厂MySQL高频基本面试问题整理，用于快速查缺补漏
MySQL中什么情况下会出现索引失效？如何排查索引失效？
weixin_44382896的博客
04-25 7384
在MySQL中，索引是用来加快检索数据库记录的一种数据结构。索引失效指的是在进行查询操作时，本应该使用索引来提升查询效率的场景下，数据库没有利用索引，而是采用了全表扫描的方式，这会大大增加查询时间和系统负担。MySQL中什么情况下会出现索引失效？如何排查索引失效？回答。
MySQL 中 NULL 导致唯一键失效
ghimi的博客
02-28 3168
MySQL 唯一键为NULL 导致唯一约束失效 最近遇到一个问题，我为一张表中创建了一个唯一键，并且键中字段为空，最终导致了唯一约束失效。这里做下分析： 首先新建一张表，包含 workNo,name,age 三个字段： DROP TABLE IF EXISTS t_emp; CREATE TABLE t_emp( id int(8) not null auto_increment, work_no varchar(8) comment '工号', name varchar(255) comme
mysql 唯一键可以为 null
电商数据代码站
01-07 403
在 MySQL 中，唯一键（Unique Key）可以包含。下面是一个简单的示例，展示在 MySQL 中创建包含允许为。在上述示例中，可以看到对于唯一键列。值，但不能插入与已存在的非。唯一键值相同的记录。
MySql中 is Null段判断无效，和IFNULL()失效的处理
weixin_41435451的博客
04-01 1万+
MySql Null字段判断，和IFNULL失效的处理 不说废话举个栗子，首先看一下这个很简单的表 ). 当我使用SQL语句通过PID进行分组查询，把列转为行的时候结果展示是这样的 不管我怎么IFNULL(SID1,0) 或者 IF( SID1 is null,0,SID1)展示结果依旧是 NULL -.-!很无奈啊。 我的sql是这么写的 没有用过 MAX函数的可以直接忽略，只看IFNUL...
mysql函数IFNULL判空不生效
json8888的博客
11-26 3451
1、SELECT IFNULL(NULL,0); 2、IFNULL是判断空的 如果逗号前为NULL则返回逗号后的信息 3、SELECT IFNULL(score,0) FROM student WHERE studentid = 8; 这种情况返回结果不为0 4、解决方案 SELECT IFNULL((SELECT score FROM student WHERE studentid = 8),0); 总结：使用b方式使用IFNULL、SUM函数等需要确保有查询记录，否则也将返回null值，当然也可使用3
mysql unique不起作用_使用UNIQUE KEY的mySQL INSERT IGNORE SELECT不起作用
weixin_28873663的博客
02-07 495
尝试使用 INSERT INTO SELECT 填充空MySQL表并连接两个源表 . 希望 IGNORE 基于定义为 UNIQUE KEY 的两个目标表列插入重复行，但由于某种原因，仍会插入基于这两列的重复行 .目的地表定义：CREATE TABLE `item` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`item_id` int(10) un...
MySQL唯一约束失效深度剖析
postgres20的博客
06-06 2125
有唯一索引居然会有重复值？这是什么鬼！
mysql的unique key有三个字段，第二个为null时，会插入重复值吗
weixin_42593130的博客
01-13 468
MySQL中的unique key是用来保证表中这三个字段的值是唯一的。如果第二个字段是null，那么在插入重复值时会报错，因为null值是不唯一的。
【mysql ifnull问题】ifnull查询条件本为null时无效问题解决
赖德发的博客
12-14 2082
查询语句进常出现字段为空，查询不到情况。此时单纯用ifnull 没用。 如： select kh,sm,im from cyslkz where cjsj>= DATE_SUB(NOW(), INTERVAL 3 HOUR) and im!=0 ORDER BY cjsj desc limit 1 三个字段没有的情况下，查询就是空。 更改，需要修改成case when 判断形式，即可，这样查询不到数据，也会返回默认的0. SELECT case when (select kh from cys
mysql数据库UNIQUE索引的作用
03-18
<think>好的，我现在要回答用户关于MySQL中UNIQUE索引的问题。首先，我需要回忆UNIQUE索引的基本概念。UNIQUE索引确保列中的每个值都是唯一的，不允许重复。这和主键有些类似，但主键还有其他的约束，比如不能为NULL，而UNIQUE索引允许NULL值，不过具体允许多少个NULL可能取决于数据库的实现。比如在MySQL中，UNIQUE索引允许有多个NULL值吗？这点可能需要确认。 接下来，功能方面，除了保证唯一性，UNIQUE索引是否还有其他作用？比如优化查询速度，因为索引本身会加快查询。但用户可能更关注其唯一性的应用场景。比如用户注册时的邮箱、用户名，这些需要唯一性的字段就可以用UNIQUE索引来防止重复。 然后要考虑应用场景。比如在用户表中，手机号、身份证号这些唯一标识信息需要UNIQUE索引。另外，联合唯一索引的情况，比如一个商品和用户的组合不能重复，确保用户不能重复购买同一商品多次，这样的场景可能需要多列UNIQUE索引。 还要注意UNIQUE索引和普通索引的区别，以及和主键的区别。主键只能有一个，而UNIQUE索引可以有多个。同时，当插入或更新数据时，如果违反UNIQUE约束，数据库会抛出错误，应用程序需要处理这种情况。 另外，UNIQUE索引的结构可能和普通索引类似，使用B+树，这样在查找时效率较高。同时，覆盖索引的情况也可能适用，如果查询只需要索引包含的列，可以直接从索引中获取数据，减少回表操作。 用户可能还关心如何创建UNIQUE索引，语法是怎样的，以及何时使用。比如在CREATE TABLE时添加，或者后期用ALTER TABLE添加。例如： CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255) UNIQUE, ... ); 或者 CREATE UNIQUE INDEX idx_email ON users(email); 还需要提到索引失效的情况，比如当使用函数或者类型转换时，可能导致索引失效，但这是否适用于UNIQUE索引呢？比如在查询中使用WHERE LOWER(email) = '...'，这时候UNIQUE索引可能不会被使用，导致全表扫描。但UNIQUE索引本身的存在仍然保证数据的唯一性，只是查询时的性能可能受影响。 还有关于NULL值的处理，比如在MySQL中，UNIQUE索引允许有多个NULL值吗？根据MySQL文档，UNIQUE索引允许有多个NULL值，因为NULL被视为未知的值，所以多个NULL不算重复。但有些数据库如SQL Server则只允许一个NULL。这点需要明确说明，避免用户混淆。 另外，当有外键约束时，UNIQUE索引可以用来作为外键的引用目标，因为外键通常需要引用唯一约束的列。比如在一个表中，外键列需要引用另一个表的UNIQUE索引列，而不仅仅是主键。 最后，需要总结UNIQUE索引的优点和注意事项。比如优点包括强制数据唯一性，提高查询速度；注意事项包括插入更新时的性能开销，以及合理设计索引避免过多影响写入性能。 可能还需要举例说明应用场景，如用户表的邮箱，商品表的SKU编号，联合唯一约束如订单号和产品ID的组合不能重复等。 现在要确保回答的结构清晰，分点列出功能、作用、应用场景、注意事项，并引用提供的参考资料中的相关内容，比如覆盖索引、多列索引等。同时，生成的相关问题要围绕UNIQUE索引的扩展，比如与主键的区别、如何创建、如何处理重复值错误等。</think>### MySQL中UNIQUE索引的功能与作用 #### 1. **功能与核心作用** - **唯一性约束**：确保索引列的值唯一，禁止重复值（允许存在多个`NULL`值，具体取决于数据库实现）[^3]。 - **查询优化**：与普通索引类似，UNIQUE索引通过B+树结构加速查询，尤其在等值查询（如`WHERE email = 'xx@xx.com'`）中效率极高。 - **数据完整性**：强制业务逻辑的唯一性要求（如用户注册时避免重复邮箱或手机号）。 #### 2. **应用场景** - **单列唯一约束**： - 用户表的邮箱（`email`）、手机号（`phone`）等字段。 - 商品表的唯一编号（`sku_code`）。 - 示例： ```sql CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255) UNIQUE -- 直接定义UNIQUE约束 ); ``` - **多列联合唯一约束**： - 订单表中用户ID与商品ID的组合不能重复（防止重复下单）。 - 示例： ```sql CREATE UNIQUE INDEX idx_user_product ON orders(user_id, product_id); ``` - **外键引用目标**： 若某表需要被其他表的外键引用，且目标列非主键，可通过UNIQUE索引实现（因外键需引用唯一约束的列）[^1]。 #### 3. **与主键索引的区别** | 特性 | 主键索引 | UNIQUE索引 | |---------------------|------------------|--------------------| | **唯一性** | ✔️ | ✔️ | | **NULL值** | 不允许 | 允许（多个NULL） | | **数量限制** | 每表仅一个 | 可定义多个 | | **是否聚簇索引** | 在InnoDB中是 | 否 | #### 4. **注意事项** - **插入/更新性能**：频繁写入时，UNIQUE索引需额外检查唯一性，可能影响性能。 - **索引失效场景**： - 对索引列使用函数（如`LOWER(email)`）或类型转换时，可能无法命中索引[^2]。 - 联合索引未遵循最左前缀原则时，可能导致部分失效[^1]。 - **错误处理**：插入重复值会触发`Duplicate entry`错误，需在代码中捕获并处理（如提示“邮箱已存在”）。 #### 5. **创建与删除语法** ```sql -- 创建表时定义 CREATE TABLE products ( id INT PRIMARY KEY, sku_code VARCHAR(50) UNIQUE ); -- 后期添加索引 CREATE UNIQUE INDEX idx_sku ON products(sku_code); -- 删除索引 DROP INDEX idx_sku ON products; ``` ---
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值