The method of setting federated unique index by mysql

This article mainly introduces the method of setting joint unique index by mysql, which is very detailed and has certain reference value. Friends who are interested must finish reading it.

Mysql can set a federated unique index by using the "Alter table table name add UNIQUE index index name (field 1, field 2)" statement, which deletes duplicate records, retains one, and then establishes a federated unique index.

Joint unique index

The project needs to add a unique index to two fields of a table to ensure that the values of the two fields cannot be duplicated at the same time.

Alter table table name add UNIQUE index index name (field 1, field 2)

When duplicate data already exists in the table, an error will be reported when it is added, and the data needs to be deduplicated.

1. Find out the duplicate data first.

SELECT * FROM (SELECT field, COUNT (1) AS num FROM table GROUP BY field) temp WHERE num >

Delete manually.

2.Alter ignore table table name add UNIQUE index index name (field 1, field 2)

It deletes duplicate records (retains one), and then builds a unique index, efficient and human (untested).

Also found some relevant content:

1. Add PRIMARY KEY (primary key index)

ALTER TABLE `table_ name` ADD PRIMARY KEY (`column`)

2. Add UNIQUE (unique index)

ALTER TABLE `table_ name` ADD UNIQUE (`column`)

3. Add INDEX (general index)

ALTER TABLE `table_ name` ADD INDEX index_name (`column`)

4. Add FULLTEXT (full-text index)

Mysql > ALTER TABLE `table_ name` ADD FULLTEXT (`column`)

5. Add multi-column index

ALTER TABLE `table_ name` ADD INDEX index_name (`column1`, `column2`, `column3`) are all the contents of the method used by mysql to set the joint unique index. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!