MySQL prend-il en charge les index uniques ?

mysql prend en charge les index uniques. Dans MySQL, les index UNIQUES permettent aux utilisateurs d'imposer l'unicité des valeurs dans une ou plusieurs colonnes, évitant ainsi les valeurs en double dans une ou plusieurs colonnes d'une table ; chaque table peut avoir plusieurs index UNIQUE, et les index UNIQUE peuvent avoir plusieurs NULL. valeurs en .

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

mysql prend en charge les index uniques. Dans MySQL, un index UNIQUE empêche les valeurs en double dans une ou plusieurs colonnes d'une table.

Introduction à MySQL UNIQUE Index

Pour imposer des valeurs uniques à une ou plusieurs colonnes, nous utilisons souvent des contraintes PRIMARY KEY.

Cependant, chaque table n'a qu'une seule clé primaire. Si vous souhaitez utiliser plusieurs colonnes ou un ensemble de colonnes avec des valeurs uniques, vous ne pouvez pas utiliser de contraintes de clé primaire.

Heureusement, MySQL propose un autre type d'index appelé index UNIQUE, qui vous permet d'imposer l'unicité des valeurs dans une ou plusieurs colonnes. Contrairement aux index PRIMARY KEY, chaque table peut avoir plusieurs index UNIQUE.

Pour créer un index UNIQUE, utilisez l'instruction CREATE UNIQUE INDEX comme suit :

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

Une autre façon d'imposer l'unicité des valeurs dans une ou plusieurs colonnes consiste à utiliser une contrainte unique. Lorsque vous créez une contrainte unique, MySQL crée un index UNIQUE en arrière-plan.

Les déclarations suivantes illustrent comment créer une contrainte unique lors de la création d'une table.

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

Utilisez également UNIQUE INDEX au lieu de UNIQUE KEY. On les appelle de la même manière.

Si vous souhaitez ajouter une contrainte unique à une table existante, vous pouvez utiliser l'instruction ALTER TABLE comme suit :

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

MySQL UNIQUE Index et NULL

Contrairement aux autres systèmes de bases de données, MySQL traite les valeurs NULL comme valeur différente. Par conséquent, vous pouvez avoir plusieurs valeurs NULL dans un index UNIQUE.

C'est ainsi que MySQL est conçu. Ce n'est pas un bug, même s'il est signalé comme tel.

Un autre point important est que la contrainte UNIQUE ne s'applique pas aux valeurs NULL autres que le moteur de stockage BDB.

Exemple d'index UNIQUE MySQL

Supposons que vous souhaitiez gérer les contacts dans une application. Nous espérons également que la colonne email du tableau des contacts doit être unique.

Pour appliquer cette règle, créez une contrainte unique dans l'instruction CREATE TABLE comme suit :

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

Si vous utilisez l'instruction SHOW INDEXES, vous verrez que MySQL crée un index UNIQUE pour la colonne email.

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

Ensuite, insérez une ligne dans le tableau des contacts.

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

Maintenant, si vous essayez d'insérer des données de ligne où l'e-mail est max.su@yiibai.com, vous recevrez un message d'erreur.

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

Après avoir exécuté l'instruction ci-dessus, vous devriez voir les résultats suivants -

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

Supposons que vous souhaitiez non seulement que l'e-mail soit unique, mais également la combinaison du prénom, du nom et du téléphone. Dans ce cas, un index UNIQUE peut être créé pour ces colonnes à l'aide de l'instruction CREATE INDEX comme suit :

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

L'ajout des lignes suivantes à la table des contacts entraîne une erreur car la combinaison prénom, nom et téléphone existe déjà.

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

Après avoir exécuté l'instruction ci-dessus, vous devriez voir les résultats suivants -

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

Vous pouvez voir que les numéros de téléphone en double ne peuvent pas être insérés dans le tableau.

Impact unique d'index unique :

Création d'une table d'index 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='学生信息表';

Un nom d'index unique y est créé, ce qui signifie que cette table d'étudiant ne peut avoir qu'un seul étudiant portant le même nom.

Commande ajouter un unique :

alter table sc add unique (name);

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

Supprimer :

alter table sc drop index `name`;

Fonction d'index unique :

Insérer d'abord des données :

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

Voir à nouveau la définition de la table :

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 à ce moment-là

Exécuter à nouveau sql:

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

Regardez à nouveau la définition de la table à ce moment-là, et vous constaterez que Auto_Increment=6

unique signalera non seulement une erreur lors de l'insertion de données en double, mais entraînera également une croissance automatique de auto_increment

La différence entre clé unique et clé primaire :

Pour faire simple, clé primaire=unique+not null

La différence spécifique :

• (1) La colonne où se trouve la contrainte unique autorise les valeurs nulles , mais la colonne où se trouve la contrainte de clé primaire n'autorise pas les valeurs nulles.

• (2) Vous pouvez placer des contraintes uniques sur une ou plusieurs colonnes, et ces colonnes ou combinaisons de colonnes doivent être uniques. Cependant, la colonne sur laquelle se trouve la contrainte d'unicité n'est pas la colonne de clé primaire de la table.

• (3) La contrainte unique force la création d'un index unique sur la colonne spécifiée. Par défaut, un index non clusterisé unique est créé. Toutefois, vous pouvez également spécifier que l'index créé est un index clusterisé.

• (4) Le but de l'établissement d'une clé primaire est d'être référencée par des clés étrangères

• (5) Une table ne peut avoir qu'une seule clé primaire au maximum, mais elle peut avoir plusieurs clés uniques

.

Lorsqu'il y a un conflit clé unique, stratégies d'évitement :

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!