How to add unique index in mysql

In mysql, you can use the "create unique index index name on table name (field name)" statement to add a unique index; the unique index is similar to the ordinary index, the only difference is that the value added to the unique index must be unique , null values ​​are allowed. If it is a combined index, the combination of column values ​​must be unique.

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

How to add a unique index to mysql

create unique index 索引名称 on 表名(字段名称)

There are about five types of Mysql indexes:

• Ordinary index (INDEX): the most basic index, no Any restrictions

• Unique index (UNIQUE): Similar to "normal index", the difference is that the value of the index column must be unique, but null values ​​are allowed.

• Primary key index (PRIMARY): It is a special unique index that does not allow null values.

• Full text index (FULLTEXT): can be used for MyISAM tables. It can also be used for innodb tables after mysql5.6. It is used to retrieve text information in an article, for larger Data,generating full-text index is time-consuming and space-consuming.

• Joint (combined) index: In order to further improve the efficiency of mysql, a composite index can be established, following the "leftmost prefix" principle.

Here we look at the unique index.

We looked at the primary key index before. It is a special unique index. The difference between the two is that the primary key index cannot have null values, but the unique index can have null values.

1: Basic usage

1: The unique index can be a single column or multiple columns. Let’s look at the creation statement:

# 创建单列唯一索引
alter table sc add unique (name);
# 创建多列唯一索引
alter table sc add unique key `name_score` (`name`,`score`);

2: Delete the unique index statement:

alter table sc drop index name;

Insert code snippet here

2: The role of the unique index

1: The biggest use is to ensure The data written to the database is a unique value.

The unique value in a single column is basically the primary key.

Commonly used are generally multi-column unique indexes. For example: a user can only purchase one item of the current product. We set the user id and product id columns as unique indexes. This can prevent a user from making repeated purchases.

Recommended learning: mysql video tutorial

The above is the detailed content of How to add unique index in mysql. For more information, please follow other related articles on the PHP Chinese website!