MySQL adds a unique index to a table with Repeated Records

If you do not want repeated records in the data table, you can add a unique index for the table.
For example, the user table has two fields: user_id and user_name. If you do not want two identical user_id and user_name, you can add the Union unique index of the two fields to the user table:
Alter table user add unique index (user_id, user_name );
In this way, when the same record is added to the table, the error 1062 is returned.
However, there is a situation where the table already has n Repeated Records. At this time, we remember to add a unique index and then execute the above operations, the database will tell you that there are already repeated records and index creation fails. At this time, we can use the following operations:
Alter ignore table user add unique index (user_id, user_name );
It will delete Repeated Records (do not worry, it will keep one record), and then create a unique index, which is efficient and user-friendly.

 

Particularly, the green SQL statement is useful when duplicate data is deleted while the index is created.

View index show index from database table name

Alter table database add Index name (database field name)

Primary Key (primary key index)

Alter table 'table _ name' add primary key ('column ')

Unique (unique index)

Alter table 'table _ name' add unique ('column ')

Index (Common Index)
Mysql> alter table 'table _ name' add index index_name ('column ')

Fulltext (full-text index)
Alter table 'table _ name' add Fulltext ('column ') Multi-column Index
Alter table 'table _ name' add index index_name ('column1 ', 'column2', 'column3 ') 1. Common Index.
This is the most basic index with no restrictions. It can be created in the following ways:
(1) Create an index: Create index indexname on tablename (tablecolumns (length); for char and varchar types, the length can be smaller than the actual length of the field; For blob and text types, length must be specified, the same below.
(2) modify the table structure: Alter tablename add index [indexname] On (tablecolumns (length ))
(3) when creating a table, specify: Create Table tablename ([...], index [indexname] (tablecolumns (length ));

2. Unique index.
It is similar to the previous "normal index". The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
(1) Create an index: create unique index indexname on tablename (tablecolumns (length ))
(2) modify the table structure: Alter tablename add unique [indexname] On (tablecolumns (length ))
(3) when creating a table, specify: Create Table tablename ([...], unique [indexname] (tablecolumns (length ));

3. Primary Key Index
It is a special unique index and does not allow null values. Create Table testindex (I _testid int not null auto_increment, vc_name varchar (16) not null, primary key (I _testid )); of course, you can also use the alter command.