mysql的null值坑(UNIQUE KEY失效和判断条件失效)

一个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 !)

 

<think>好的,我现在要回答用户关于MySQLUNIQUE索引的问题。首先,我需要回忆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>### MySQLUNIQUE索引的功能与作用 #### 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、付费专栏及课程。

余额充值