mysql은 고유 인덱스를 지원합니까?

mysql은 고유 인덱스를 지원합니다. MySQL에서 UNIQUE 인덱스를 사용하면 사용자는 하나 이상의 열에 있는 값의 고유성을 강화하여 테이블에 있는 하나 이상의 열에 있는 값의 중복을 방지할 수 있습니다. 각 테이블에는 여러 개의 UNIQUE 인덱스가 있을 수 있으며 UNIQUE 인덱스에는 여러 개의 NULL이 있을 수 있습니다. .

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

mysql은 고유 인덱스를 지원합니다. 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 KEY 대신 UNIQUE INDEX를 사용하세요. 그들은 같은 것으로 불립니다.

기존 테이블에 고유 제약 조건을 추가하려면 다음과 같이 ALTER TABLE 문을 사용할 수 있습니다.

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

MySQL UNIQUE 인덱스 및 NULL

다른 데이터베이스 시스템과 달리 MySQL은 NULL 값을 처리합니다. 다른 가치로. 따라서 UNIQUE 인덱스에는 여러 개의 NULL 값을 가질 수 있습니다.

MySQL은 이렇게 설계되었습니다. 이는 버그로 보고되었더라도 버그가 아닙니다.

또 다른 중요한 점은 BDB 스토리지 엔진 이외의 NULL 값에는 UNIQUE 제약 조건이 적용되지 않는다는 것입니다.

MySQL UNIQUE 인덱스 예

애플리케이션에서 연락처를 관리하고 싶다고 가정해 보세요. 또한 연락처 테이블의 이메일 열은 고유해야 합니다.

이 규칙을 적용하려면 다음과 같이 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이 이메일 열에 대한 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

다음으로 연락처 테이블에 행을 삽입하세요.

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'

이메일이 고유해야 할 뿐만 아니라 이름, 성, 전화번호의 조합도 고유해야 한다고 가정합니다. 이 경우 다음과 같이 CREATE INDEX 문을 사용하여 이러한 열에 대해 UNIQUE 인덱스를 생성할 수 있습니다.

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

연락처 테이블에 다음 행을 추가하면 first_name, last_name 및 전화 조합이 이미 존재하기 때문에 오류가 발생합니다.

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'

테이블에 중복된 전화번호를 삽입할 수 없음을 알 수 있습니다.

고유 인덱스 고유 영향:

고유 인덱스 테이블 생성:

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='学生信息表';

고유 인덱스 이름이 생성되는데, 이는 이 학생 테이블에 동일한 이름을 가진 학생이 하나만 있을 수 있음을 의미합니다.

명령 추가 고유:

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가 자동으로 커지게 한다는 것을 알 수 있습니다

고유 키와 기본 키의 차이점:

간단히 말하면 기본 키=고유+null 아님

구체적인 차이점:

(1) 고유 제약 조건이 있는 열은 null 값을 허용합니다. 이지만 기본 키 제약 조건이 있는 열은 null 값을 허용하지 않습니다.
(2) 하나 이상의 열에 고유 제약 조건을 적용할 수 있으며 이러한 열 또는 열 조합은 고유해야 합니다. 그러나 고유 제약 조건이 있는 열은 테이블의 기본 키 열이 아닙니다.
(3) 고유 제약 조건은 지정된 열에 고유 인덱스를 강제로 생성합니다. 기본적으로 고유한 비클러스터형 인덱스가 생성되지만 생성된 인덱스가 클러스터형 인덱스가 되도록 지정할 수도 있습니다.
(4) 기본 키를 설정하는 목적은 외래 키를 참조하는 것입니다.
(5) 테이블에는 기본 키가 최대 하나만 있을 수 있지만 고유 키는 여러 개 있을 수 있습니다

고유한 키 충돌이 있는 경우 회피 전략:

insert ignore:

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

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

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

replace into:

• replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
• 使用replace into，你必须具有delete和insert权限

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

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

insert on duplicate key update:

• 如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。
• 使用insert into，你必须具有insert和update权限
• 如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0

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，如

解决办法：

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

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

结论：

• 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
• insert ignore能忽略重复数据，只插入不重复的数据。
• replace into和insert ... on duplicate key update，都是替换原有的重复数据，区别在于replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；insert ... on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

【相关推荐：mysql视频教程】

위 내용은 mysql은 고유 인덱스를 지원합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!