Does mysql support unique indexes?

mysql supports unique indexes. In MySQL, UNIQUE indexes allow users to enforce uniqueness of values ​​in one or more columns, preventing duplicate values ​​in one or more columns in a table; each table can have multiple UNIQUE indexes, and UNIQUE indexes There can be multiple NULL values ​​in .

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

mysql supports unique indexes. In MySQL, a UNIQUE index prevents duplicate values ​​in one or more columns in a table.

Introduction to MySQL UNIQUE Index

To enforce unique values ​​for one or more columns, we often use PRIMARY KEY constraints.

However, each table has only one primary key. If you want to use multiple columns or a set of columns with unique values, you cannot use primary key constraints.

Fortunately, MySQL provides another type of index called a UNIQUE index, which allows you to enforce uniqueness of values ​​in one or more columns. Unlike PRIMARY KEY indexes, each table can have multiple UNIQUE indexes.

To create a UNIQUE index, use the CREATE UNIQUE INDEX statement as follows:

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

Another way to enforce uniqueness of values ​​in one or more columns is to use a unique constraint. When you create a unique constraint, MySQL creates a UNIQUE index behind the scenes.

The following statements illustrate how to create a unique constraint when creating a table.

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

You can also use UNIQUE INDEX instead of UNIQUE KEY. They are called the same thing.

If you want to add a unique constraint to an existing table, you can use the ALTER TABLE statement as follows:

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

MySQL UNIQUE Index and NULL

Unlike other database systems, MySQL treats NULL values ​​as different values. Therefore, you can have multiple NULL values ​​in a UNIQUE index.

This is how MySQL is designed. This is not a bug, even though it is reported as one.

Another important point is that the UNIQUE constraint does not apply to NULL values ​​other than the BDB storage engine.

MySQL UNIQUE Index Example

Suppose you want to manage contacts in an application. We also hope that the email column in the contacts table must be unique.

To implement this rule, create a unique constraint in the CREATE TABLE statement as follows:

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

If you use the SHOW INDEXES statement, you will see that MySQL creates a UNIQUE for the email column index.

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

Next, insert a row into the contacts table.

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

Now, if you try to insert row data where the email is max.su@yiibai.com, you will receive an error message.

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

After executing the above statement, you should see the following results -

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

Suppose you not only want the email to be unique, but also the combination of first_name, last_name and phone to be unique. In this case, you can use the CREATE INDEX statement to create a UNIQUE index for these columns as follows:

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

Adding the following rows to the contacts table results in an error because of the combination of first_name, last_name and phone existed.

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

After executing the above statement, you should see the following results -

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

You can see that duplicate phone numbers cannot be inserted into the table.

Unique index unique impact:

Unique index table creation:

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

A unique index name is created in it, which means that there can only be one student with the same name in this student table.

Command to add unique:

alter table sc add unique (name);

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

Delete:

alter table sc drop index `name`;

Unique index function:

Insert some data first :

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

Check the table definition again:

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

At this time Auto_Increment=5

Execute sql again:

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

Check the table definition again at this time, You will find that Auto_Increment=6

unique will not only report an error when inserting duplicate data, but also cause auto_increment to automatically grow

unique and primary key The difference:

Simply put, primary key=unique not null

The specific difference:

• (1) The column where the unique constraint is located allows null values, but the column where the primary key constraint is located does not allow null values.

• (2) You can place unique constraints on one or more columns, and these columns or combinations of columns must be unique. However, the column on which the unique constraint is located is not the primary key column of the table.

• (3) The unique constraint forces the creation of a unique index on the specified column. By default, a unique non-clustered index is created, however, you can also specify that the index created is a clustered index.

• (4) The purpose of establishing a primary key is to allow foreign keys to reference it.

• (5) A table can only have one primary key at most, but it can There are many unique keys

When there is a unique key conflict, avoidance strategy:

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

The above is the detailed content of Does mysql support unique indexes?. For more information, please follow other related articles on the PHP Chinese website!