Home  >  Article  >  Database  >  How to add unique index in mysql

How to add unique index in mysql

WBOY
WBOYOriginal
2022-05-12 18:09:4624005browse

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.

How to add unique index in mysql

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn