Unterstützt MySQL eindeutige Indizes?

MySQL unterstützt eindeutige Indizes. In MySQL können Benutzer mit UNIQUE-Indizes die Eindeutigkeit von Werten in einer oder mehreren Spalten erzwingen und doppelte Werte in einer oder mehreren Spalten in einer Tabelle verhindern. Jede Tabelle kann mehrere UNIQUE-Indizes haben, und UNIQUE-Indizes können mehrere NULL-Werte haben Werte in .

Die Betriebsumgebung dieses Tutorials: Windows7-System, MySQL8-Version, Dell G3-Computer.

MySQL unterstützt eindeutige Indizes. In MySQL verhindert ein UNIQUE-Index doppelte Werte in einer oder mehreren Spalten einer Tabelle.

Einführung in den MySQL UNIQUE Index

Um eindeutige Werte für eine oder mehrere Spalten zu erzwingen, verwenden wir häufig PRIMARY KEY-Einschränkungen.

Allerdings hat jede Tabelle nur einen Primärschlüssel. Wenn Sie mehrere Spalten oder eine Reihe von Spalten mit eindeutigen Werten verwenden möchten, können Sie keine Primärschlüsseleinschränkungen verwenden.

Glücklicherweise bietet MySQL einen anderen Indextyp namens UNIQUE-Index, mit dem Sie die Eindeutigkeit von Werten in einer oder mehreren Spalten erzwingen können. Im Gegensatz zu PRIMARY KEY-Indizes kann jede Tabelle mehrere UNIQUE-Indizes haben.

Um einen UNIQUE-Index zu erstellen, verwenden Sie die CREATE UNIQUE INDEX-Anweisung wie folgt:

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

Eine andere Möglichkeit, die Eindeutigkeit von Werten in einer oder mehreren Spalten zu erzwingen, ist die Verwendung einer eindeutigen Einschränkung. Wenn Sie eine Unique-Einschränkung erstellen, erstellt MySQL im Hintergrund einen UNIQUE-Index.

Die folgenden Anweisungen veranschaulichen, wie man beim Erstellen einer Tabelle eine eindeutige Einschränkung erstellt.

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

Verwenden Sie auch UNIQUE INDEX anstelle von UNIQUE KEY. Sie heißen gleich.

Wenn Sie einer vorhandenen Tabelle eine eindeutige Einschränkung hinzufügen möchten, können Sie die ALTER TABLE-Anweisung wie folgt verwenden:

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

MySQL UNIQUE Index und NULL

Im Gegensatz zu anderen Datenbanksystemen behandelt MySQL NULL-Werte als unterschiedlicher Wert. Daher kann ein UNIQUE-Index mehrere NULL-Werte enthalten.

So ist MySQL konzipiert. Dies ist kein Fehler, auch wenn er als solcher gemeldet wird.

Ein weiterer wichtiger Punkt ist, dass die UNIQUE-Einschränkung nicht für andere NULL-Werte als die BDB-Speicher-Engine gilt.

Beispiel für einen MySQL UNIQUE-Index

Angenommen, Sie möchten Kontakte in einer Anwendung verwalten. Wir hoffen auch, dass die E-Mail-Spalte in der Kontakttabelle eindeutig sein muss.

Um diese Regel durchzusetzen, erstellen Sie eine eindeutige Einschränkung in der CREATE TABLE-Anweisung wie folgt:

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)
);

Wenn Sie die SHOW INDEXES-Anweisung verwenden, werden Sie sehen, dass MySQL einen UNIQUE-Index für die E-Mail-Spalte erstellt.

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

Fügen Sie als Nächstes eine Zeile in die Kontakttabelle ein.

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

Wenn Sie nun versuchen, Zeilendaten dort einzufügen, wo die E-Mail-Adresse max.su@yiibai.com lautet, erhalten Sie eine Fehlermeldung.

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

Nachdem Sie die obige Anweisung ausgeführt haben, sollten Sie die folgenden Ergebnisse sehen:

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

Gehen Sie davon aus, dass nicht nur die E-Mail eindeutig sein soll, sondern auch die Kombination aus Vorname, Nachname und Telefonnummer eindeutig sein soll. In diesem Fall kann mit der CREATE INDEX-Anweisung wie folgt ein UNIQUE-Index für diese Spalten erstellt werden:

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

Das Hinzufügen der folgenden Zeilen zur Kontakttabelle führt zu einem Fehler, da die Kombination aus Vorname, Nachname und Telefon bereits vorhanden ist.

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

Nachdem Sie die obige Anweisung ausgeführt haben, sollten Sie die folgenden Ergebnisse sehen:

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

Sie können sehen, dass doppelte Telefonnummern nicht in die Tabelle eingefügt werden können.

Einzigartige Auswirkungen auf den Index:

Einzigartige Indextabellenerstellung:

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

Ein eindeutiger Indexname wird darin erstellt, was bedeutet, dass diese Schülertabelle nur einen Schüler mit demselben Namen haben kann.

Befehl zum Hinzufügen von Unique:

alter table sc add unique (name);

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

Löschen:

alter table sc drop index `name`;

Eindeutige Indexfunktion:

Fügen Sie zuerst einige Daten ein:

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);

Sehen Sie sich die Tabellendefinition erneut an:

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

Zu diesem Zeitpunkt ist Auto_Increment= 5

Führen Sie sql erneut aus:

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

Schauen Sie sich zu diesem Zeitpunkt noch einmal die Tabellendefinition an. Sie werden feststellen, dass Auto_Increment = 6

unique nicht nur einen Fehler beim Einfügen doppelter Daten meldet, sondern auch dazu führt, dass auto_increment automatisch wächst

Der Unterschied zwischen eindeutigem und Primärschlüssel:

Einfach ausgedrückt: Primärschlüssel = eindeutig + nicht null

Der spezifische Unterschied:

• (1) Die Spalte, in der sich die eindeutige Einschränkung befindet, erlaubt Nullwerte, aber die Spalte, in der sich die Primärschlüsseleinschränkung befindet, lässt keine Nullwerte zu.

• (2) Sie können eindeutige Einschränkungen für eine oder mehrere Spalten festlegen, und diese Spalten oder Spaltenkombinationen müssen eindeutig sein. Die Spalte, in der sich die eindeutige Einschränkung befindet, ist jedoch nicht die Primärschlüsselspalte der Tabelle.

• (3) Die Unique-Einschränkung erzwingt die Erstellung eines eindeutigen Index für die angegebene Spalte. Standardmäßig wird ein eindeutiger nicht gruppierter Index erstellt. Sie können jedoch auch angeben, dass es sich bei dem erstellten Index um einen gruppierten Index handelt.

• (4) Der Zweck der Erstellung eines Primärschlüssels besteht darin, durch Fremdschlüssel referenziert zu werden

• (5) Eine Tabelle kann höchstens einen Primärschlüssel haben, aber sie kann viele eindeutige Schlüssel haben

Wenn es einen einzigartigen Schlüsselkonflikt gibt, Vermeidungsstrategien:

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视频教程】

Das obige ist der detaillierte Inhalt vonUnterstützt MySQL eindeutige Indizes?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!