精华内容
下载资源
问答
  • 唯一索引可以为null
    千次阅读
    2021-01-19 09:42:12

    线上问题:org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [update fl_table set id = ?, password = ?, email = ? where id = '3583954800']; Duplicate entry ' ' for key 'email'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ' ' for key 'email

    更新email字段为空时出现错误,查数据库发现表的email字段加了唯一索引,最多有一条为空或者其他值的数据

    解决办法:唯一索引可在索引列插入多次null

    email可以有多条email为null的记录,将冲突的字段设置为null,解决

    在sql server中,唯一索引字段不能出现多个null值

    在mysql 的innodb引擎中,是允许在唯一索引的字段中出现多个null值的。

    根据NULL的定义,NULL表示的是未知,因此两个NULL比较的结果既不相等,也不不等,结果仍然是未知。根据这个定义,多个NULL值的存在应该不违反唯一约束,所以是合理的,在oracel也是如此。

    更多相关内容
  • mysql 唯一索引null.md

    千次阅读 2021-01-21 09:17:09
    mysql 的唯一索引要求所有参与的列都不能够 null 值,如果唯一索引中的任何一个元素含有 null 值,则唯一约束将不起作用。示例代码create table tb (a int,b int,c int,unique index (a,b,c));insert into tb(a,b,...

    mysql 的唯一索引要求所有参与的列都不能够为 null 值,如果唯一索引中的任何一个元素含有 null 值,则唯一约束将不起作用。

    示例代码

    create table tb (

    a int,

    b int,

    c int,

    unique index (a,b,c)

    );

    insert into tb(a,b,c) values (null,null,null); -- ok

    insert into tb(a,b,c) values (null,null,null); -- still ok

    insert into tb(a,b,c) values (null,null,null); -- still ok

    insert into tb(a,b,c) values (1,null,null); -- ok

    insert into tb(a,b,c) values (1,2,null); -- ok

    insert into tb(a,b,c) values (1,2,3); -- ok

    insert into tb(a,b,c) values (1,null,null); -- SHOULD FAIL, BUT DOESN'T

    insert into tb(a,b,c) values (1,2,null); -- SHOULD FAIL, BUT DOESN'T

    insert into tb(a,b,c) values (1,2,3); -- fails correctly

    解决方案

    给参与唯一索引的字段设置缺省值,如果是数值可以统一设置为 -1,如果是字符串可以设置为 ""

    官网的描述

    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 permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

    展开全文
  • Oracle唯一索引NULL空值之间的关系

    千次阅读 2022-04-28 00:12:44
    碰巧看到墨天轮上的一篇小文,标题是"Oracle的唯一索引为什么允许多个null行数据的存在?",虽然知识点小,但从问题,再到作者的解释,就给我们做出了一个表率。问题描述如下,一同事提出Oracle中,一个表上有唯一...

    我们学习知识和原理,是否理解到位,重要的就是能不能将其运用到实际工作中,做到举一反三,融会贯通。

    碰巧看到墨天轮上的一篇小文,标题是"Oracle的唯一索引为什么允许多个null行数据的存在?",虽然知识点小,但从问题,再到作者的解释,就给我们做出了一个表率。

    问题描述如下,

    一同事提出Oracle中,一个表上有唯一索引,该列上就应该加上非空约束,他应该是在比对数据时遇到了问题,按照唯一索引列去比较,却没有比较出列值为null的数据,想当然的觉得应该让研发改规范,要把唯一索引列上加上非空约束。


    我一听这个,这跟我以往使用的经验不符啊,Oracle是允许唯一索引列上,多行都是null值的。这个是我做java代码开发时就已知的事实,但当时就觉得数据库就是这样设计的,没去想为什么是这样。我当时就回复了那个同事一下,说数据库的唯一索引就是这样设计的,允许多个null存在,多个null不算重复。

    实验如下所示,

    SQL> create table test_unique_index(c1 number, c2 varchar2(1), c3 varchar2(1));
    Table created.
    
    
    SQL> create unique index idx_test_unique_index_01 on test_unique_index(c1, c2, c3);
    Index created.
    
    
    SQL> insert into test_unique_index values(1, 'a', null);
    1 row created.
    
    
    SQL> insert into test_unique_index values(1, 'a', null);
    insert into test_unique_index values(1, 'a', null)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (BISAL.IDX_TEST_UNIQUE_INDEX_01) violated

    可以看到,(1, 'a', null)记录不允许插入两次,这是不是就说多个null是重复的?他的同事继续说,

    他说你看复合唯一索引时,有一个列为null出现了两次,Oracle却认为违反了唯一索引,这不就是认为多个null列也算重复吗,Oracle这样处理的是不是有点反常?

    官方手册对唯一索引的说明,明确指出Oracle不会对所有列都是null的行进行索引存储,换句话说,如果不是说所有列均为null,就可以索引存储,

    https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895

    Unique and Nonunique Indexes

    Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or columns. For example, no two employees can have the same employee ID. Thus, in a unique index, one rowid exists for each data value. The data in the leaf blocks is sorted only by key.


    Nonunique indexes permit duplicates values in the indexed column or columns. For example, the first_name column of the employees table may contain multiple Mike values. For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid (ascending).


    Oracle Database does not index table rows in which all key columns are null, except for bitmap indexes or when the cluster key column value is null.

    作者的回复,

    什么是唯一索引?


    唯一索引是说的唯一索引列的数据唯一还是索引段中存储的索引唯一,这样一想瞬间就明白了,索引唯一当然指的是索引段中存储的索引唯一。


    Oracle为什么允许单列唯一索引中数据可以多行为null?那是因为null的数据不会存入索引。

    Oracle为什么处理复合唯一索引时,有一个列为null出现了两次,Oracle却认为违反了唯一索引约束?那是因为复合索引列,只要有一列不为空就会存入索引中。

    我们尝试dump上述索引数据块,可以看到,确实索引叶子节点数据块存储了(1, 'a', null)的数据,

    ...
    row#XXX[XXXX] flag: -------, lock: 2, len=14, data:(6):  03 00 2b d7 00 00
    col 0; len 1; (1):  61
    col 1; len 2; (2):  c1 02
    col 2; NULL
    ...

    近期更新的文章:

    NFT,只可远观不可亵玩焉

    MySQL数据库设计开发规范

    SQL Server检索SQL和用户信息的需求

    MySQL隐式转换案例一则

    Linux几种数据传输工具比较

    文章分类和索引:

    公众号900篇文章分类和索引

    展开全文
  • 据库默认值都有null,此时创建唯一索引时要注意了,此时数据库会把空作为多个重复值
  • MySql索引失效-联合唯一索引null

    千次阅读 2021-07-13 00:59:22
    今天在测试过程中发现有数据重复的现象,想设计一个唯一索引达到幂等的效果,但是发现设置了唯一索引以后还是可以插入相同的数据,经过排查,发现联合唯一索引中含有null值会导致索引失效。 二.问题复现 现在我们来...

    一.背景

    今天在测试过程中发现有数据重复的现象,想设计一个唯一索引达到幂等的效果,但是发现设置了唯一索引以后还是可以插入相同的数据,经过排查,发现联合唯一索引中含有null值会导致索引失效。

    二.问题复现

    现在我们来复现一下这个场景。

    2.1 首先创建一个 user 表,指定 user_name + address 为联合唯一索引

    CREATE TABLE `t_user` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `user_name` varchar(256) NOT NULL COMMENT '用户名',
      `sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别: 0:男,1:女',
      `address` varchar(256) NULL COMMENT '住址',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_name_address` (`user_name`,`address`) COMMENT '唯一键:姓名+地址'
    ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
    

    在这里插入图片描述
    2.2 插入一行数据

    INSERT INTO `t_user` (`user_name`, `sex`, `address`) values ('dkangel', 0, 'SZ');
    

    在这里插入图片描述
    2.3 插入同样一条数据,发现mysql报错:
    ERROR 1062 (23000): Duplicate entry 'dkangel-SZ' for key 't_user.uk_name_address'
    在这里插入图片描述
    4. 再插入address不同的数据

    INSERT INTO `t_user` (`user_name`, `sex`, `address`) values ('dkangel', 0, 'HZ');
    

    在这里插入图片描述
    2.5 到这里一切都还是正常的,联合唯一索引都是生效的

    2.6 让我们再插入一条 address 为空的数据

    INSERT INTO `t_user` (`user_name`, `sex`) values ('dkangel_yc', 0);
    

    在这里插入图片描述
    7. 再插入一样的数据,发现插入成功了,联合唯一索引在值有为 null 的情况下失效
    在这里插入图片描述

    问题解决

    1. 不要将可能为 null 的值设置为联合索引的一部分
    2. 用特殊值来代替可能为空的值,如:数字类型用0,字符串类型用空字符等

    在本例中,只需要将 address 的默认值设置为空字符""即可

    展开全文
  • MYSQL 引擎innodb ,设置了唯一索引的列值 允许 多个NULL值存在。 示例: 字段 userCardNum 添加了唯一索引 证实是允许存在的多个NULL值数据的: 原由: 因为这里 NULL 的定义 ,是指 未知值。 所以多...
  • 今天正在吃饭,一个朋友提出了一个他面试中遇到的问题,MySQL允许在唯一索引字段中添加多个NULL值。 这个问题对于我一个非专业DBA来说,也没特地去验证过,所以正好借此机会验证一下,做个记录: 测试环境: ...
  • mysql unique index 允许为null

    千次阅读 2020-11-07 00:56:11
    根据业务需要给数据库表添加一个多字段联合唯一索引 unique index。使用表的phone, shop_code 字段关联创建。 其中表phone字段允许null,默认为null。 操作: 使用sql执行脚本执行添加索引sql。执行时报错。提示有...
  • mysql唯一索引null

    千次阅读 2021-06-09 13:54:30
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`,`age`) ) ENGINE=InnoDB AUTO_INCREMENT=6 ...
  • 《Oracle唯一索引NULL空值之间的关系》提到了当存在唯一索引的时候,不能插入两条(1, 'a', null),但是有朋友说,MySQL允许,实测一下,root@mysqldb:[test]>createtablett1(c1varchar(1),c2varchar(1),c3...
  • mysql组合唯一索引空值和NUll

    千次阅读 2019-03-07 18:59:23
    MySQL 多字段组合唯一索引中,有一列字段可能会出现空值 业务场景: **用户行为记录表,**防止用户数据重复插入表中。也防止尴尬。。。 直接上SQL,已优化 CREATE TABLE `user_behavior` ( `behavior_id` bigint(20...
  • mysql 联合唯一索引null

    千次阅读 2020-06-19 11:21:33
    举个例子 表a中,b,c联合索引,但是下表这种状态是存在的,而且还可以继续插入1,null 这种数据 index,b,c 1,1,null 2,1,null
  • mysql唯一约束特点是不能重复允许为null ...-- 唯一索引(不设置not null) create table demo1( id int unsigned primary key auto_increment, phone_no char(11) , unique key uk_phone_no (`phone_no`) using btr
  • create table TEST(col_1 INT,col_2 INT,col_3 VARCHAR(128) not null,col_4 VARCHAR(128) not null,UNIQUE KEY (col_1, col_2, col_3),CONSTRAINT FOREIGN KEY (col_1) REFERENCES T2(ID) ON DELETE CASCAD...
  • mysql字段为NULL索引是否会失效

    千次阅读 2022-04-04 17:47:57
    1、创建表sc_base_color,其中普通索引为 “name,group_num”,这里暂时不测组合索引,下面再测试。 CREATE TABLE `sc_base_color` ( `id` bigint NOT NULL AUTO_INCREMENT, `group_num` bigint DEFAULT
  • MySQL 中 NULL 导致唯一键失效

    千次阅读 2022-02-28 10:08:00
    MySQL 唯一为NULL 导致唯一约束失效 最近遇到一个问题,我一张表中创建了一个唯一键,并且键中字段空,最终导致了唯一约束失效。这里做下分析: 首先新建一张表,包含 workNo,name,age 三个字段: DROP TABLE ...
  • 【MongoDB】索引属性 之 唯一索引

    千次阅读 2020-06-21 13:27:28
    唯一索引可确保索引字段不存储重复值,即强制索引字段的唯一性。默认情况下,MongoDB在创建集合期间会在_id字段上创建唯一索引
  • 别踩坑!使用MySQL唯一索引请注意

    万次阅读 多人点赞 2019-01-28 21:33:14
    背景 在程序设计中了,我们往往需要...但是我们不能确保同时有两个人使用同一个手机号注册到我们的系统中,因此这里就需要在更深的层次去确保手机号的唯一性了。不同存储方案,解决方式不一样,这里以MySQL例,我...
  • 请高手帮忙解惑,谢谢唯一索引:UNIQUEKEY`uwu`(`UserId`,`WeixinOpenid`,`UnionID`),1:表结构信息如下:CREATETABLE`t_weixin`(`WeixinId`bigint(20)NOTNULLAUTO_INCREMENT,`MallId`bigint(20)DEFAULT...
  • Oracle唯一索引功能替代

    千次阅读 2021-05-03 10:08:10
    Oracle唯一索引在字段全部为NULL时,不做唯一性判断,允许重复插入,而在8t中即使均为NULL值也会做重复值判断,在某些场景下客户会存在此类需求,在数据量不大不存在性能问题的情况下可以考虑通过如下方式进行替代...
  • 创建时不是联合索引,而是唯一索引(player_id),更改联合索引 CREATE TABLE `willow_player` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `player_id` bigint(16) NOT NULL DEFAULT '0' unique, `award_type...
  • 有一个表,要创建两个列的唯一索引,但是有一列可以为空,这时要分两种情况建两个索引。如果只建一个,那么当parent_id空时,即使name不同,也可以重复。 也就是说加索隐时也可以使用where条件。 CREATE TABLE ...
  • 当Mysql中建立的联合索引, 只要索引中的某一列的值空时(NULL),即便其他的字段完全相同,也不会引起唯一索引冲突。 原因: Mysql官方文档中有这样的解释 A UNIQUE index creates a constraint such that all ...
  • 前段时间我踩过一个坑:在mysql8的一张innodb引擎的表中,加了唯一索引,但最后发现数据竟然还是重复了。到底怎么回事呢?本文通过一次踩坑经历,聊聊唯一索引,一些有意思的知识点。
  • 一 主键和唯一索引都要求值唯一,但是...主键不能为null唯一索引可以为null; ⑤.主键可以外键,唯一索引不行; 二 主键约束比唯一索引约束严格,当没有设定主键时,非空唯一索引自动称为主键。对于主键和唯一...
  • 谈谈唯一约束和唯一索引

    千次阅读 2021-01-27 20:54:06
    约束 全称完整性约束,它是关系数据库中的对象,用来存放插入到一个表中一列数据的规则,用来确保数据的准确性和一致性。索引 数据库中用的最频繁的操作是数据查询,索引...和主键约束不同,唯一约束允许 NULL,只...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 272,061
精华内容 108,824
关键字:

唯一索引可以为null