mysql支不支持unique索引

发布时间:2023-04-04 17:33:48 作者:iii
来源:亿速云 阅读:228

这篇文章主要讲解了“mysql支不支持unique索引”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“mysql支不支持unique索引”吧!

mysql支持unique索引。在MySQL中,UNIQUE索引允许用户在一个或多个列中强制实现值的唯一性,可以防止表中一个或多个列中拥有重复的值;每个表可以有多个UNIQUE索引,且UNIQUE索引中可以具有多个NULL值。

mysql支持unique索引。在MySQL中,UNIQUE索引可以防止表中一个或多个列中拥有重复的值。

MySQL UNIQUE索引简介

要强制执行一个或多个列的唯一性值,我们经常使用PRIMARY KEY约束。

但是,每个表只有一个主键。 如果要使用多个列或一组具有唯一值的列,则不能使用主键约束。

幸运的是,MySQL提供了另一种称为UNIQUE索引的索引,它允许您在一个或多个列中强制实现值的唯一性。 与PRIMARY KEY索引不同,每个表可以有多个UNIQUE索引。

要创建一个UNIQUE索引,请使用CREATE UNIQUE INDEX语句如下:

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

在一个或多个列中强制实现值的唯一性的另一种方法是使用唯一约束。创建唯一约束时,MySQL会在幕后创建一个UNIQUE的索引。

以下语句说明了如何在创建表时创建唯一约束。

CREATE TABLE table_name(
...
   UNIQUE KEY(index_column_,index_column_2,...) 
);

也可以使用UNIQUE INDEX而不是UNIQUE KEY。 它们被称为相同的东西。

如果要向现有表添加唯一约束,可以使用ALTER TABLE语句,如下所示:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

MySQL UNIQUE索引和NULL

与其他数据库系统不同,MySQL将NULL值视为不同的值。 因此,您可以在UNIQUE索引中具有多个NULL值。

这就是MySQL的设计方式。 这不是一个错误,即使它被报告为一个错误。

另一重要的一点是UNIQUE约束不适用于除了BDB存储引擎之外的NULL值。

MySQL UNIQUE索引示例

假设想在应用程序中管理联系人。还希望contacts表中的email列必须是唯一的。

要执行此规则,请在CREATE TABLE语句中创建唯一的约束,如下所示:

USE testdb;

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY unique_email (email)
);

如果使用SHOW INDEXES语句,将看到MySQL为email列创建了一个UNIQUE索引。

SHOW INDEXES FROM contacts;

+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| contacts |          0 | PRIMARY      |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| contacts |          0 | unique_email |            1 | email       | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

接下来,向contacts表中插入一行。

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','max.su@yiibai.com');

现在,如果您尝试插入电子邮件是max.su@yiibai.com的行数据,您将收到一条错误消息。

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','max.su@yiibai.com');

执行上面语句后,应该会看到以下结果 -

1062 - Duplicate entry 'max.su@yiibai.com' for key 'unique_email'

假设您不仅希望电子邮件是唯一的,而且first_name,last_name和phone的组合也是唯一的。 在这种情况下,可以使用CREATE INDEX语句为这些列创建一个UNIQUE索引,如下所示:

CREATE UNIQUE INDEX idx_name_phone
ON contacts(first_name,last_name,phone);

将以下行添加到contacts表中会导致错误,因为first_name,last_name和phone的组合已存在。

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES('Max','Su','(+86)-999-9988','john.d@yiibai.com');

执行上面语句后,应该会看到以下结果 -

1062 - Duplicate entry 'Max-Su-(+86)-999-9988' for key 'idx_name_phone'

可以看到,不可以将重复电话号码插入到表中。

唯一性索引unique影响:

唯一性索引表创建:

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

在其中创建了唯一索引name,就是说这个学生表同名的学生只能由一位。

命令添加unique:

alter table sc add unique (name);

alter table sc add unique key `name_score` (`name`,`score`);

删除:

alter table sc drop index `name`;

唯一性索引作用:

先行插入部分数据:

insert into sc (name,class,score) values ('吕布','一年二班',67);
insert into sc (name,class,score) values ('赵云','一年二班',90);
insert into sc (name,class,score) values ('典韦','一年二班',89);
insert into sc (name,class,score) values ('关羽','一年二班',70);

再次查看表定义:

show create table sc;

CREATE TABLE `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

这时的Auto_Increment=5

再次执行sql:

insert into sc (name,class,score) values ('吕布','二年二班',77)
> 1062 - Duplicate entry '吕布' for key 'name'
> 时间: 0.01s

此时再次查看表定义,会发现Auto_Increment=6

unique除了在插入重复数据的时候会报错,还会使auto_increment自动增长

unique与primary key的区别:

简单的讲,primary key=unique+not null

具体的区别:

存在唯一键冲突时,避免策略:

insert ignore:

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断),如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据.

insert ignore into sc (name,class,score) values ('吕布','二年二班',77)

执行上面的语句,会发现并没有报错,但是主键还是自动增长了。

replace into:

replace into sc (name,class,score) values ('吕布','二年二班',77);

此时会发现吕布的班级跟年龄都改变了,但是id也变成最新的了,所以不是更新,是删除再新增

insert on duplicate key update:

insert into sc (name,class,score) values ('关羽','二年二班',80) on duplicate key update score=100;
> Affected rows: 2
> 时间: 0.008s

旧数据中关羽是一年二班,70分,现在插入,最后发现只有分数变成了100,班级并没有改变。

4	关羽	一年二班	100	2018-11-16 15:32:18		2018-11-16 15:51:51

id没有发生变化,数据只更新,但是auto_increment还是增长1了。

死锁:

insert ... on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,
如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,
然后对该记录加上X(排他锁),最后进行update写入。

如果有两个事务并发的执行同样的语句,
那么就会产生death lock,如

mysql支不支持unique索引

解决办法:

1、尽量对存在多个唯一键的table使用该语句

2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论:

感谢各位的阅读,以上就是“mysql支不支持unique索引”的内容了,经过本文的学习后,相信大家对mysql支不支持unique索引这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!

推荐阅读:
  1. MySQL慢日志的意思是什么
  2. MySQL索引知识有哪些

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql unique

上一篇:win10鼠标右键菜单如何设置

下一篇:服务器linux系统如何安装

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》