精华内容
下载资源
问答
  • 此时创建唯一索引时要注意了,此时数据库会把作为多个重复值,而创建索引失败,示例如下: 步骤1: mysql> select phone ,count(1) from User group by phone; +—————–+———-+ | phone | count(1) | +——...
  • oracle的索引字段可以设置为空

    千次阅读 2017-03-08 15:39:13
    答案是肯定的; 可以建,就是有点不合理; 索引是告诉你有什么,并不会告诉你没有什么; 如果少数几条null无所谓,如果很多null/很多重复值建索引的意义就打折扣了
     
    答案是肯定的;
    可以建,就是有点不合理;
    索引是告诉你有什么,并不会告诉你没有什么;
    如果少数几条null无所谓,如果很多null/很多重复值建索引的意义就打折扣了 
    展开全文
  • 当Mysql中建立的联合索引, 只要索引中的某一列的值为空时(NULL),即便其他的字段完全相同,也不会引起唯一索引冲突。 原因: Mysql官方文档中有这样的解释 A UNIQUE index creates a constraint such that all ...

    问题:

    当Mysql中建立的联合索引, 只要索引中的某一列的值为空时(NULL),即便其他的字段完全相同,也不会引起唯一索引冲突。

    原因:

    Mysql官方文档中有这样的解释

    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. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

    唯一约束对NULL值不适用。原因可以这样解释: 比如我们有一个单列的唯一索引,既然实际会有空置的情况,那么这列一定不是NOT NULL的,如果唯一约束对空值也有起作用,就会导致仅有一行数据可以为空,这可能会和实际的业务需求想冲突的,所以通常Mysql的存储引擎的唯一索引对NULL值是不适用的。 这也就倒是联合唯一索引的情况下,只要某一列为空,就不会报唯一索引冲突。

    解决方案:

    给会为空的列定义一个为空的特殊值来表示NULL,比如数字类型使用0值,字符串类型使用空字符串。

    展开全文
  • 别踩坑!使用MySQL唯一索引请注意

    万次阅读 多人点赞 2019-01-28 21:33:14
    背景 在程序设计中了,我们往往需要...但是我们不能确保同时有两个人使用同一个手机号注册到我们的系统中,因此这里就需要在更深的层次去确保手机号的唯一性了。不同存储方案,解决方式不一样,这里以MySQL例,我...

    背景

    在程序设计中,我们往往需要确保数据的唯一性,比如在常见的注册模块,我们需要确保一个手机号只能注册为一个账号。这种情况下,我们的程序往往是第一道关卡,用户来注册之前,首先判断这个手机号是否已经注册,如果已经注册则返回错误信息,或直接去登录。但是我们不能确保同时有两个人使用同一个手机号注册到我们的系统中,因此这里就需要在更深的层次去确保手机号在系统的唯一性了。不同存储方案,解决方式不一样。对于常用的MySQL数据库,我们可以使用唯一索引的方式来作为我们的最后一道防线。

    但是最近在使用数据库的唯一索引时,发现一个比较奇怪的现象。MySQL数据库,使用InnoDB存储引擎,创建了唯一索引时,在insert操作时,如果唯一索引上的字段有为NULL的情况,则可以无限插入。这有点匪夷所思,但是现实就是这么一个情况。现在就来具体分析这样的一个案例,来看看底层对于唯一索引是怎么设计的,来规避在数据库设计上犯错和踩坑。

    案例

    假设现在有一个用于保存用户信息的数据表user,是使用email注册的,当前使用email作为唯一索引,同时这一基本规则也被其他依赖系统作为设计数据模型的设计基础。假设现在设计这样一个user表:

    CREATE TABLE `user` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
      `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',
      `name` varchar(11) DEFAULT '' COMMENT 'name',
      `age` int(11) DEFAULT NULL COMMENT 'age',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk-email` (`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    1@user.com来注册,执行insert语句,执行成功

    INSERT INTO user (email,name,age) VALUES ('1@user.com','h1',18);
    

    1@user.com再来注册,则再次执行,则报错。成功规避了用户多次创建导致系统产生脏数据问题。

    Duplicate entry '1@user.com' for key 'uk-email'
    

    从这里看,user表的设计是符合业务要求的,并没有出现同一个email出现多行的情况。随着业务发展,单单email注册的模式并不适合移动互联网时代,所以现在的要求在原有基础上增加了手机号的字段,并要求手机号也是唯一的。于是添加phone字段,并将原有唯一索引删除,为email和phone设置新的唯一索引。

    ALTER TABLE `user` ADD COLUMN `phone` varchar(11) default NULL AFTER `age`;
    
    DROP INDEX `uk-email` ON `user`;
    
    ALTER TABLE `user` ADD UNIQUE KEY `uk-email-phone` (`email`,`phone`);
    

    假设用户1再来用同样的email注册,可以注册成功:

    INSERT INTO user (email,name,age,phone) VALUES (‘1@user.com’,‘h1’,18,NULL);

    查询数据库数据,得到以下结果:
    mysql-query-user-uk-index-1
    有两个email为1@user.com的记录,他们的phone都是NULL,这怎么可能存在?!难道是MySQL出问题了?!不可能,我们再试另外一个数据

    INSERT INTO user (email,name,age,phone) VALUES ('2@user.com','h2',18,'18812345678');
    

    连续执行两次,第一次执行成功,第二次报错:

    Duplicate entry ‘2@user.com-18812345678’ for key ‘uk-email-phone’

    查询user结果集,得到
    mysql-query-user-uk-index-2
    从结果看这样MySQL的唯一索引也算是正常的啊,那这到底是怎么一回事呢?

    原因探寻

    业务中希望建立的唯一索引是email + phone的组合,但是由于phone一开始是没有数据的,所以新建字段时默认允许为NULL来兼容老数据。如果程序没有控制好,数据操作直接打到数据库,就产生了两条email为“1@user.com”且phone为NULL的数据,那么就会发生这种数据错乱的情况。

    我从 MySQL 5.7官方文档 中找到了这个:

    Unique Indexes

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

    官方的文档中明确说明在唯一索引中是允许存在多行值为NULL的数据存在的。

    当然我们会认为这是MySQL的一个bug,其实早有人这么认为了,并给MySQL提出了这个问题https://bugs.mysql.com/bug.php?id=8173。但是MySQL的开发者并不认为这是一个bug,而是本身的一种设计。额,这么说,好像也说得过去。那这里就有一个问题了,我们知道索引是使用B+树来维护的,但是对于这种非唯一索引是怎么维护的?

    带着这个问题,我觉得有两种可能:

    (1)唯一索引时另外一种数据类型,正好把有值为NULL的字段过滤掉了,无需特殊处理。

    (2)还是用的B+树索引,但是对于NULL的索引特殊处理了。

    于是我对email=2@user.com且phone= 18812345678的数据执行了Explain执行计划

    explain select * from user where `email` = '2@user.com' and `phone` = '18812345678';
    

    mysql-query-user-uk-index-3
    这个查询正好用到了唯一索引uk-email-phone,索引长度是134。

    对email=1@user.com且phone为NULL的执行类似Explain执行计划

    explain select * from user where `email` = '1@user.com' and `phone` is   NULL;
    

    mysql-query-user-uk-index-4

    对比上面两次不同数据的explain执行结果,可以看到其实都用了uk-email-phone的唯一索引,不同的是第一个type是const(通过一次索引就可以找到,用于primary key或unique index),第二个type是ref(非唯一性索引扫描),且rows为2。所以猜测这里极有可能是对NULL进行的特殊处理,唯一索引树还是用的和非NULL一样的唯一索引树。

    源码分析

    上面利用explain,测试结果是符合自己的猜测行为而已。也许只有源码中才能比较好的知道答案,基于此,在github上找到MySQL相关的源码(在此感谢DBA同学在唯一索引源码分析上的指点)。在这段源码https://github.com/mysql/mysql-server/blob/8e797a5d6eb3a87f16498edcb7261a75897babae/storage/innobase/row/row0ins.cc中,有一个方法 row_ins_scan_sec_index_for_duplicate(),这里会扫描唯一非聚簇索引树,来确定是否会发生唯一性的冲突。源码内有一段注释

    /* If the secondary index is unique, but one of the fields in the
      n_unique first fields is NULL, a unique key violation cannot occur,
      since we define NULL != NULL in this case */
    

    在继续往下有一段这样的逻辑

    	 cmp = cmp_dtuple_rec(entry, rec, index, offsets);
    
        if (cmp == 0 && !index->allow_duplicates) {
          if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) {
            err = DB_DUPLICATE_KEY;
    
            thr_get_trx(thr)->error_info = index;
    
            /* If the duplicate is on hidden FTS_DOC_ID,
            state so in the error log */
            if (index == index->table->fts_doc_id_index &&
                DICT_TF2_FLAG_IS_SET(index->table, DICT_TF2_FTS_HAS_DOC_ID)) {
              ib::error(ER_IB_MSG_958) << "Duplicate FTS_DOC_ID"
                                          " value on table "
                                       << index->table->name;
            }
    
            goto end_scan;
          }
        } else {
          ut_a(cmp < 0 || index->allow_duplicates);
          goto end_scan;
        }
    

    跳转到row_ins_dupl_error_with_rec()方法中有一段这样的逻辑

    /* In a unique secondary index we allow equal key values if they
      contain SQL NULLs */
    
      if (!index->is_clustered() && !index->nulls_equal) {
        for (i = 0; i < n_unique; i++) {
          if (dfield_is_null(dtuple_get_nth_field(entry, i))) {
            return (FALSE);
          }
        }
      }
    

    在唯一索引中有字段为NULL的情况下,返回FALSE,代码中就没有抛出DB_DUPLICATE_KEY的异常了。所以从源码来看,这里实现了唯一索引允许为NULL的情况了,而且可以知道,这个唯一索引树和其他的二级索引基本上是没什么区别的。这也是前面explain时及时我们查询非唯一索引中另一个字段为空的记录,也还是用到了同样的索引和相同的索引长度。

    反观来看,如果是我们在未知实现的情况下,要我们来设计,怎么实现允许有字段为NULL的唯一索引呢?是否还有比现有MySQL更好的方式来实现?

    结论

    所以其实MySQL在唯一索引中允许存在值为NULL的字段。NULL值在MySQL可以代表是任意值,并且在有字段值为NULL时,不会参与校验这个组合的唯一索引,所以可能插入业务上不允许重复的数据,导致脏数据。

    因此在创建属于唯一索引的列时,最好指定字段值不能为空,在已有值为NULL的情况下,创建的字段不允许为空,且默认值为空字符。如果已经创建了默认值为NULL的字段,则先将其update为空字符,然后再修改为NOT NULL DEFAULT ‘’。如上述情况建表语句改为

    CREATE TABLE `user` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
      `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',
      `name` varchar(11) DEFAULT '' COMMENT 'name',
      `age` int(11) DEFAULT NULL COMMENT 'age',
      `phone` varchar(11) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk-email-phone` (`email`,`phone`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    并非所有数据库都是这样,SQL Server 2005及更老的版本,只允许有一个NULL值出现。从https://sqlite.org/faq.html#q26 了解到ANSI SQL-92标准:

    A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.(如果且仅当表中没有两行在唯一列中具有相同的非空值时,才满足唯一约束。)
    

    除了MySQL之外,sqlLite、PostgreSQL、Oracle和FireBird也是允许唯一索引上存在多行为NULL。

    展开全文
  • mysql组合唯一索引空值和NUll

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

    MySQL 多字段组合唯一索引中,有一列字段可能会出现空值

    业务场景:

    **用户行为记录表,**防止用户数据重复插入表中。也防止尴尬。。。

    直接上SQL,已优化

    CREATE TABLE `user_behavior` (
      `behavior_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '行为ID',
      `channel` smallint(5) NOT NULL DEFAULT '0' COMMENT '渠道:0,蓝虎APP订单详情',
      `order_id` bigint(20) DEFAULT NULL COMMENT '订单ID',
      `shop_user_id` binary(16) NOT NULL COMMENT '用户id',
      `doc_id` bigint(20) NOT NULL COMMENT '文档ID',
      `state` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用户行为状态:0,主动 1:强制',
      `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`behavior_id`),
      UNIQUE KEY `index_shop_user_doc_id` (`channel`,`shop_user_id`,`doc_id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COMMENT='用户行为记录';
    

    在这里插入图片描述

    组合唯一索引,其中有一个字段会出现空值或NULL值

    直接上SQL,其中order_id会出现NULL情况。

    CREATE TABLE `user_behavior` (
      `behavior_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '行为ID',
      `channel` smallint(5) NOT NULL DEFAULT '0' COMMENT '渠道:0,蓝虎APP订单详情',
      `order_id` bigint(20) DEFAULT NULL COMMENT '订单ID',
      `shop_user_id` binary(16) NOT NULL COMMENT '用户id',
      `doc_id` bigint(20) NOT NULL COMMENT '文档ID',
      `state` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用户行为状态:0,主动 1:强制',
      `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`behavior_id`),
      UNIQUE KEY `index_shop_user_doc_id` (`order_id`,`shop_user_id`,`doc_id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COMMENT='用户行为记录';
    

    在这里插入图片描述

    结论

    组合唯一索引,其中有一个字段会出现NULL值,做防重复数据,是有问题的,所以组合唯一索引中不能出现NULL值存在。NOT NULL

    展开全文
  • mysql 联合唯一索引和null

    千次阅读 2020-06-19 11:21:33
    举个例子 表a中,b,c联合索引,但是下表这种状态是存在的,而且还可以继续插入1,null 这种数据 index,b,c 1,1,null 2,1,null
  • 常用的数据库都是树结构的索引,本篇的背景也全部以树结构的索引为前提。本文旨在梳理各种常见的索引类型,简明扼要地说明它们的区别与联系,不讨论数据结构与算法。话不多说,直接上干货。 索引 数据库只做两件事情...
  • 当Mysql中建立的联合索引, 只要索引中的某一列的值为空时(NULL),即便其他的字段完全相同,也不会引起唯一索引冲突。 原因: Mysql官方文档中有这样的解释 A UNIQUE index creates a constraint such that all ...
  • 唯一约束和唯一索引区别

    千次阅读 2017-09-21 21:02:55
    3) 主键列上没有任何两行具有相同值(即重复值),不允许(NULL).4) 主健可作外健,唯一索引不可; 2.唯一性约束(UNIQUE) 1) 唯一性约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问...
  • 主键和唯一索引的区别

    千次阅读 2018-03-01 00:00:54
    3.主键不允许空值,唯一索引可以为空;4.主键可以被其他表引用,而唯一索引可以;5.一个表最多只能创建一个主键,而可以创建多个唯一索引;6.主键和索引都是键,主键是逻辑键,索引物理键,即主键不实际存在。 ...
  • 今天正在吃饭,一个朋友提出了一个他面试中遇到的问题,MySQL允许在唯一索引字段中添加多个NULL值。 这个问题对于我一个非专业DBA来说,也没特地去验证过,所以正好借此机会验证一下,做个记录: 测试环境: ...
  • SQLServer索引管理——唯一索引和唯一约束的区别 唯一索引保证在索引键列中的值是唯一的 唯一约束保证没有重复值被插入到列中,当该列上创建有唯一约束的时候。当列上创建了唯一约束时,对应的会在该列自动创建...
  • oracle之唯一索引

    万次阅读 2018-01-03 11:38:43
    1 oracle支持唯一索引可以理解唯一约束。用来控制和约束需要插入的表中字段。(主键自动唯一约束,且不为空 ...则可以将这些字段添加为唯一索引。unique 表示唯一 如:create unique index user_password_ind
  • 适当的业务操作场景使用适当的索引方案可以显著的提升系统整体性能和用户体验。在Oracle中,索引有包括很多类型。不同类型的索引适应不同的系统环境和访问场景。其中,唯一索引Unique Index是我们经常使用到的一种...
  • 索引

    千次阅读 多人点赞 2018-09-07 23:27:20
    类型:普通索引,唯一索引,主键索引,复合索引,聚族索引。 唯一索引:不允许具有索引值相同的行,即每一行数据的索引的值唯一。 复合索引:对多列添加一个索引。 复合索引遵循最左原则,即创建复合索引时的第一...
  • 唯一索引与主键索引的比较

    千次阅读 2019-02-20 23:02:51
    唯一索引 唯一索引不允许两行具有相同的索引值。 如果现有数据中存在重复的键值,则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时,数据库也拒绝接受此数据。例如,如果在 ...
  • mysql中unique创建唯一索引

    万次阅读 2018-06-24 21:30:19
    主键约束要保证数据既不可以为空也不可重复,而UNIQUE唯一索引,是可以允许数据为空的,而且唯一索引的主要作用就是避免数据重复(不是提高效率哦)。 本人也目前也是小白一个,在网上查得有人建表时这样添加唯一...
  • 主键索引可以为空 主键索引可以做外键 一张表中只能有一个主键索引 普通索引: 用来加速数据访问速度而建立的索引。多建立在经常出现在查询条件的字段和经常用于排序的字段。 被索引的数据列允许包含重复的值 ...
  • 浅谈主键索引与唯一索引

    千次阅读 2017-11-16 18:57:38
    主键索引与唯一索引 一、主键索引定义 主键索引是唯一索引的特殊类型。 数据库表通常有一列或列组合,其值用来唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义一个主键将自动创建主键索引,...
  • 主键索引和唯一索引的区别

    千次阅读 2018-09-26 19:09:54
    主键约束(PRIMARY KEY): 1.主键用于唯一的标识表中的每一...4.主键可作外键,唯一索引不可。 唯一约束(UNIQUE): 1.唯一约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问某行的可选手段,一个表...
  • 主键约束、唯一性约束、唯一索引

    千次阅读 2018-07-22 00:23:40
    1.主键约束(PRIMARY KEY) ...4) 主健可作外健,唯一索引不可; 2.唯一性约束(UNIQUE) 1) 唯一性约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问某行的可选手段,一个表上可以放置多...
  • ORA-00001: 违反唯一约束条件 这一个报错相信大家在插入数据时还是经常遇到的,尤其是在测试环境。 但是今天我在处理一个生产问题的时候再次遇到这个报错时有点奇怪: 1.该表(记表A)的主键是数据库序列生成的,...
  • 主键ID,可以一列或多列,主键既是约束也是索引且是唯一索引,同时也用于对象缓存的键值。 2、索引 组合或者引用关系的子表(数据量较大的时候),需要在关联主表的列上建立非聚集索引(如订单明细表中的产品...
  • 索引为什么提高了查询的速率,但是update delete insert 的速度会变慢 因为:这三种操作会破坏二叉树的,所以会造成速度变慢 什么样的索引使用什么样的方法进行查询 对于模糊查询 1 使用全文索引的目的就是为了...
  • 如果在一个列上同时建唯一索引和普通索引的话,mysql会自动选择唯一索引。 谷歌一下: 唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。 补充下概念:1、普通索引(非唯一索引) 普通索引(由...
  • ** 因为本人之前一直写的是...后续再将它们改成博客的形式,争取2天至少改一篇博客,觉得我总结的还行的可以先关注我,后续会发成博客形式,内容也会更加完善 ** 索引索引就是用来提高数据库性能的东西,不用加内...
  • Mysql唯一索引 唯一约束

    万次阅读 2017-09-28 23:37:42
    Mysql唯一索引 唯一约束唯一索引作为mysql众多索引常用的一种,再一次业务中了解到此索引特在此记载Mysql唯一索引 唯一约束 唯一索引的的作用 唯一索引与唯一约束的区别 添加删除唯一索引的sql语句 需要注意的坑唯一...
  • ... 索引是一种特殊的文件,它们包含着对所有记录的引用指针。索引可以极大地提高数据查询速度,但是会降低插入删除更新表的速度,因为在执行这些操作是,还要操作索引文件用来维护,所以说索引不是建的越多...
  • 添加索引 1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD ...2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE (`column`) 3.添加INDEX(普通索引) mysql>ALT...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 192,480
精华内容 76,992
关键字:

唯一索引可以为空吗