-
mysql唯一索引能为空吗_mysql 允许在唯一索引的字段中出现多个null值
2021-01-19 09:42:12解决办法:唯一索引可在索引列插入多次null email可以有多条email为null的记录,将冲突的字段设置为null,解决 在sql server中,唯一索引字段不能出现多个null值 在mysql 的innodb引擎中,是允许在唯一索引的字段中...线上问题: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 unique index 允许为null
2020-11-07 00:56:11其中表phone字段允许null,默认为null。 操作: 使用sql执行脚本执行添加索引sql。执行时报错。提示有创建唯一约束字段存在重复值。 排查问题: SELECT COUNT(DISTINCT `phone`, `shop_code`) from customer; 1最近遇到个问题是关于mysql 数据库 unique key 唯一约束索引添加的问题。
问题场景:
根据业务需要给数据库表添加一个多字段联合唯一索引 unique index。使用表的phone, shop_code 字段关联创建。
其中表phone字段允许null,默认为null。操作:
使用sql执行脚本执行添加索引sql。执行时报错。提示有创建唯一约束字段存在重复值。
排查问题:
SELECT COUNT(DISTINCT `phone`, `shop_code`) from customer;
1. 使用该sql查询需要创建unique index 的字段的过滤重复总数是否 等于 count(*) 全表总数。发现不相等,脚本报错。查看数据,发现phone值存在大量为null的数据。
2. 然后查询mysql使用手册,官方设定mysql 的 unique索引时允许包含多个null值。
以下时官方在常见unique的文档的解释,大意是说:唯一索引 创建一个UNIQUE索引时,创建字段的值必须不能重复。但是一个UNIQUE 索引,可以包含NULL值
原文:
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. If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as follows: _rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used. Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.
官方文档连接: https://dev.mysql.com/doc/refman/5.6/en/create-index.html.
得到结论,mysql 唯一索引是支持多个null值存在的。
解决:
修复脚本的重复数据校验逻辑,过滤空值。
SELECT COUNT(DISTINCT `phone`, `shop_code`) FROM customer WHERE phone is not null;
脚本成功执行,唯一索引成功添加。 -
mysql 唯一索引_MySQL 唯一索引的使用
2021-01-18 18:19:251、MySQL 唯一索引的使用普通索引允许被索引的数据列包含重复的值。唯一索引则是不允许有重复的值,当然 null 除外,唯一索引不仅仅可以存储 null , 还可以存储多个 null。如果能确定某个数据列将只包含彼此各不...1、MySQL 唯一索引的使用
普通索引允许被索引的数据列包含重复的值。唯一索引则是不允许有重复的值,当然 null 除外,唯一索引不仅仅可以存储 null , 还可以存储多个 null。
如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。
这么做的好处:
1)简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;
2)MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了,如果是,MySQL将拒绝插入那条新记录。
总之:唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。个人建议,数据关系应该不仅仅使用主键来标记记录的唯一性,而且也要使用逻辑主键来确定唯一性,也就是把逻辑主键建立唯一索引。
2、唯一索引用来更新
对于数据保存操作(新增或更新),我们一般使用先删除后插入,因为这样操作简单。但是其实这样也不简单,要是更新操作的时候只有逻辑主键没有物理主键,那么查询也需要分情况编写。但是如果你有逻辑主键且作为了唯一索引,那么就可以使用 MySQL 的 ON DUPLICATE KEY UPDATE
或者 REPLACE INTO
1)ON DUPLICATE KEY UPDATE
如果您指定了 ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个 UNIQUE 索引 或PRIMARY KEY中出现重复值,则执行 UPDATE。
A:单条插入
例如,如果列a被定义为Primary 或 UNIQUE,并且包含值1,则以下两个语句具有相同的效果:
mysql>INSERT INTO table (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
mysql>UPDATE table SET c=c+1 WHERE a=1;
B:多条插入
批量插入时,可以使用如下语句进行重复则更新,没有则插入。
mysql>INSERT INTO table (a,c) VALUES (1,3),(2,4)
ON DUPLICATE KEY UPDATE c = VALUES(c);
2)REPLACE INTO
使用REPLACE插入一条记录时,通过判断 primary key 或 unique 索引 ,如果没有重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。
A:单条插入
例如,如果列a被定义为Primary 或 UNIQUE,并且包含值1,则以下两个语句具有相同的效果:
mysql>REPLACE INTO table (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
mysql>UPDATE table SET c=c+1 WHERE a=1;
B:多条插入
批量插入时,可以使用如下语句进行重复则更新,没有则插入。
mysql>REPLACE INTO table (a,c) VALUES (1,3),(2,4)
总结,如果是需要字段关联更新那么使用 ON DUPLICATE KEY UPDATE,但是如果是已经知道更新的值了那么直接使用 REPLACE INTO 更简单。
3、真实案例:错题本重复生成问题
错题本有个字段 exam_base_id 建立了普通的索引,
然后我一直以为如果用索引字段作为条件进行删除那么这些数据肯定会被锁死,换句话说下一个操作语句过来肯定需要等待,这样理解本身没错,前提是必须要有对应的数据被锁,如果本身就没有数据那么,Mysql又如何锁住这些数据呢?
且看下面的执行记录(前提 select * from t_w where exam_base_id = 10 没有任何记录 )
t1
t2
delete from t_w where exam_id = 10
delete from t_w where exam_id = 10
insert into t_w select ... where exam_id = 10
insert into t_w select ... where exam_id = 10
commit
commit
select * from t_w 结果会发现有重复的记录,道理就是上面说的,无论事务1还是事务2,在通过exam_id删除数据时,因为没有记录被锁定,所以导致两个事务可以同时执行,为了解决重复数据问题,我觉得用唯一索引会比较好
总结:唯一索引不但可以调高查询效率而且可以保证数据的唯一性!!
参考:
http://lobert.iteye.com/blog/1604122
-
mysql 唯一约束和唯一索引_谈谈唯一约束和唯一索引
2021-01-27 20:54:06约束 全称完整性约束,它是关系数据库中的对象,用来存放插入到一个表中一列数据的规则,用来确保数据的准确性和一致性。索引 数据库中用的最频繁的操作是数据查询,索引...和主键约束不同,唯一约束允许为 NULL,只...约束 全称完整性约束,它是关系数据库中的对象,用来存放插入到一个表中一列数据的规则,用来确保数据的准确性和一致性。
索引 数据库中用的最频繁的操作是数据查询,索引就是为了加速表中数据行的检索而创建的一种分散的数据结构。可以把索引类比成书的目录,有目录的肯定比没有目录的书,更方便查找。
唯一约束 保证在一个字段或者一组字段里的数据都与表中其它行的对应数据不同。和主键约束不同,唯一约束允许为 NULL,只是只能有一行。
唯一索引 不允许具有索引值相同的行,从而禁止重复的索引或键值。
唯一约束和唯一索引都是为了保证表中被限制的字段不允许有重复的值,看起来功能是一样的,那为什么要设计这样两种一样的功能呢?
首先创建两个字段值一样的表 t1,t2,并为 t1 表中的 col1 列设置唯一约束。
CREATE TABLE t1 (
col1 INT(11),
col2 VARCHAR(20),
CONSTRAINT t1_uq UNIQUE (col1)
);
CREATE TABLE t2 (
col1 INT(11),
col2 VARCHAR(20)
);
然后为表 t2 表中的 col1 列设置唯一索引
CREATE UNIQUE INDEX
t2_idx ON t2 (col1);
创建表 t3,并将 t1 表中的 col1 列设置为 t3 表中 col2 列的外键
CREATE TABLE t3 (
col1 INT(11),
col2 INT(11),
col3 VARCHAR(20),
CONSTRAINT t3_fk FOREIGN KEY (col2) REFERENCES t1 (col1)
);
到此为止,基本上就能得出,唯一约束和唯一索引在 MySQL 数据库里区别了
概念上不同,约束是为了保证数据的完整性,索引是为了辅助查询;
创建唯一约束时,会自动的创建唯一索引;
在理论上,不一样,在实际使用时,基本没有区别。
关于第二条,MySQL 中唯一约束是通过唯一索引实现的,为了保证没有重复值,在插入新记录时会再检索一遍,怎样检索快,当然是建索引了,所以,在创建唯一约束的时候就创建了唯一索引。
4.唯一性索引
如果确定某个数据列只包含彼此各不相同的值,在为这个数据列创建索引的时候,就应该用关键字UNIQUE把它定义为一个唯一索引,Mysql会在有新纪录插入数据表时,自动检查新纪录的这个字段的值是否已经在某个记录的这个字段里出现过了。如果是,mysql将拒绝插入那条新纪录。也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复
5.索引的优点
5.1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
5.2.建立索引可以大大提高检索的数据,以及减少表的检索行数
5.3.在表连接的连接条件,可以加速表与表直接的相连
5.4.在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
5.5.建立索引,在查询中使用索引,可以提高性能。
CREATE TABLE parent(2
3id INT NOT NULL,4
5PRIMARY KEY (id)6
7 ) TYPE=INNODB;8
9CREATE TABLE child(10
11id INT, parent_id INT,12
13INDEX par_ind (parent_id),14
15FOREIGN KEY (parent_id) REFERENCES parent(id)16
17ON DELETE CASCADE18
19 ) TYPE=INNODB;
如下是一个更复杂的例子,其中一个product_order表对其它两个表有外键。一个外键引用一个product表中的双列索引。另一个引用在customer表中的单行索引:
1 CREATE TABLE product ( 2 3 category INT NOT NULL, 4 5 id INT NOT NULL, 6 7 price DECIMAL, 8 9 PRIMARY KEY(category, id)10 11 ) TYPE=INNODB;12 13 CREATE TABLE customer (id INT NOT NULL,14 15 PRIMARY KEY (id)16 17 ) TYPE=INNODB;18
19 CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,20 21 product_category INT NOT NULL,22 23 product_id INT NOT NULL,24 25 customer_id INT NOT NULL,26 27 PRIMARY KEY(no), 28 29 INDEX (product_category, product_id),30 31 FOREIGN KEY (product_category, product_id) 32 33 REFERENCES product(category, id)34 35 ON UPDATE CASCADE ON DELETE RESTRICT,36 37 INDEX (customer_id), 38 39 FOREIGN KEY (customer_id)4041 REFERENCES customer(id)42 43 ) TYPE=INNODB;
-
mysql 删除联合唯一索引吗_mysql唯一索引和联合索引
2021-01-27 20:29:45在mysql 的innodb引擎中,是允许在唯一索引的字段中出现多个null值的。根据NULL的定义,NULL表示的是未知,因此两个NULL比较的结果既不相等,也不不等,结果仍然是未知。根据这个定义,多个NULL值的存在应该不违反..... -
唯一索引
2018-04-28 16:20:35如果将多个字段一起设置为 唯一索引,如1、job 和 deptno 不允许具有索引值相同的行。如:2、但是其中有一个是可以相同的3、不过null除外,两个都是null 的话,记录可以相同。如:... -
mysql唯一索引什么意思_MySQL唯一索引什么意思
2021-01-19 10:51:28如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该使用关键字UNIQUE,把它定义为一个唯一索引。创建唯一索的方法操作表CREATE TABLE `wb_blog` (`id` smallint(8) unsigned NOT ... -
mysql 唯一约束和唯一索引_谈谈唯一约束和唯一索引(示例代码)
2021-02-07 16:20:28约束 全称完整性约束,它是关系数据库中的对象,用来存放插入到一个表中一列数据的规则,用来确保数据的准确性和一致性。索引 数据库中用的最频繁的操作是数据查询,索引...和主键约束不同,唯一约束允许为 NULL,只... -
mysql中的唯一索引
2021-01-31 16:47:14主键值不允许为null,而唯一索引允许 一个表只能有一个主键,唯一索引不一定 主键和唯一索引可以是表中多个字段的组合,一般只会在一列上建立唯一索引,多列的话则会考虑联合索引 关于唯一索引和主键区别的一些资 -
约束2:主键约束,唯一约束和唯一索引
2019-09-29 05:08:23主键约束和唯一约束都会创建唯一索引,不同之处在于主键约束的索引键在定义上不允许为NULL,而唯一约束的索引键在定义上允许为NULL;唯一约束认为索引键上的NULL值是相等的,不允许重复的NULL值出现在索引键或其组合... -
唯一约束和唯一索引区别
2017-09-21 21:02:553) 主键列上没有任何两行具有相同值(即重复值),不允许空(NULL).4) 主健可作外健,唯一索引不可; 2.唯一性约束(UNIQUE) 1) 唯一性约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问... -
Constraint1:主键约束,唯一约束和唯一索引
2016-05-12 17:25:00主键约束和唯一约束都会创建唯一索引,不同之处在于主键约束的索引键在定义上不允许为NULL,而唯一约束的索引键在定义上允许为NULL;唯一约束认为索引键上的NULL值是等待的,不允许重复的NULL值出现在索引键或其组合... -
主键、唯一键、唯一索引区别
2017-05-17 22:14:09不允许空(NULL),主健可作外健,唯一索引不可; 2.定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。 唯一键(唯一性约束): 1.唯一性约束用来限制不受主键约束的列上的数据的唯一性,用于... -
MySQL中有唯一性约束的列能否为null
2017-01-16 14:55:11唯一索引中允许有null. 参考链接 -
主键索引和唯一索引的区别
2018-09-26 19:09:54主键约束(PRIMARY KEY): 1.主键用于唯一的标识表中的每一...4.主键可作外键,唯一索引不可。 唯一约束(UNIQUE): 1.唯一约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问某行的可选手段,一个表... -
mysql的索引
2021-03-30 23:17:26主键索引是特殊的唯一索引,也就是主键索引不能为null,但唯一索引允许为null 如果是在innodb存储引擎的话,构建的通常是聚簇索引(也就是数据跟表结构存放在一起)。也就是叶子节点存放的是表中的数据。 如果是在... -
主键约束、唯一约束和唯一索引
2019-10-04 02:29:352)是不可能(或很难)更新.3)主键列上没有任何两行具有相同值(即重复值),不允许空(NULL).4)主健可作外健,唯一索引不可;2.唯一性约束(UNIQUE)1)唯一性约束用来限制不受主键约束的列上的数据的唯一性,用于... -
主键约束、唯一性约束、唯一索引
2018-07-22 00:23:401.主键约束(PRIMARY KEY) ...4) 主健可作外健,唯一索引不可; 2.唯一性约束(UNIQUE) 1) 唯一性约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问某行的可选手段,一个表上可以放置多... -
MySQL中的唯一性约束与NULL详解
2021-01-19 22:54:50这时 A,B,C 三列都是不允许 NULL 值的,唯一性约束也是 work 的。 后来由于需求的变化,修改了以前的唯一性约束,又多加了一列。(至于为什么加就不赘述了)。 ALTER TABLE testTable DROP INDEX IDX_UN_LOAN_PLAN_... -
数据库索引-系列文章(8)-数据库允许空值(null),往往是悲剧的开始
2021-01-26 21:13:15数据库字段允许空值,会遇到一些问题,此处包含的一些知识点,和大家聊一聊。 1. 数据准备: create table user ( id int, name varchar(20), index(id) )engine=innodb;...id为索引,非唯一(non u -
数据库索引
2021-01-12 09:18:32唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2);... -
数据库键(key)、主键(primaryKey)、索引(index)、唯一索引(uniqueIndex)区别_Allione_新浪博客...
2020-02-24 11:28:271、键:key,数据库的物理结构,一是约束(偏重于约束和规范数据库的结构完整性),二是索引(辅助查询用的)。...不允许为null,不允许有重复值; 可以作为外键; 主键自动创建聚簇索引; 选择:主键值应... -
Mysql踩坑记之为什么能不null就不null
2020-08-25 23:12:26大家都知道,mysql的字段有个属性是是否允许为NULL,我们可以设置为default null 或者not null 或者not null default ‘’ 下面我列一下这三个表示方法的利弊。 1、default null 这种写法,会造成我们根据当前列... -
MySQL添加、修改、删除索引以及索引的使用场景
2020-06-09 16:11:16UNIQUE(唯一索引):不可以出现相同的值,可以有NULL值;( 加快查询速度,并且可以限制列值的唯一性,允许为空 唯一索引包括联合唯一索引,多列形成的唯一索引,这些列可以唯一确定一条表记录,可以为空 ) ... -
mysql索引
2019-11-20 09:56:19primary: 唯一索引,不允许为null。 normal: 普通非唯一索引。 unique: 表示唯一的,不允许重复的索引,可以为null。 fulltext: 表示全文搜索的索引。 FULLTEXT用于搜索很长一篇文章的时候,效果最好。用在比较... -
索引
2019-03-20 17:48:001、索引的分类有那些 (1)创建普通索引(`间隔符) ...(2)创建唯一索引【】这一列中不能有重复值,允许为null】 create unique index 索引名 on `表名`(`列名`) alter table `表名` add u... -
Oralce索引
2020-01-01 22:29:43无论是唯一性索引还是非唯一性索引,索引值都允许为NULL。在默认情况下,Oracle创建的索引是非唯一性索引。 当在表中定义主键约束或唯一性约束时,Oralce会自动在相应列上创建唯一性索引。 平衡树索引与位图索引 ... -
mysql中常见的几种索引
2021-02-27 16:14:53唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2);... -
mysql索引类型
2019-11-21 11:05:11primary:唯一索引,不允许为null (高并发可尝试) key:普通非唯一索引。 unique:表示唯一的,不允许重复的索引,可以为null。 fulltext: 表示全文搜索的索引。 FULLTEXT用于搜索很长一篇文章的时候,效果最好。... -
五、索引
2021-01-06 14:22:51(2)、唯一索引(UNIQUE):加速查询 + 列值唯一(值允许为Null) (3)、普通索引(NORMAL):仅加速查询 (4)、组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并 (5)、全文索引(FULLTEXT):...