How to add a joint unique index using MySQL

Joint unique index

Joined unique index needs to be used in the project:

For example: there is the following requirement: each person can only generate one record per day: outside the program agreement, The database itself can also be set:

For example: there are two fields aa and bb in the t_aa table. If you do not want to have two identical records (ie: the value of the aa field can be repeated; the value of the bb field can also be Can be repeated, but the combined value of a record (aa, bb) is not allowed to be repeated), you need to add a joint unique index of multiple fields to the t_aa table:

alter table t_aa add unique index(aa,bb);

For example:

alter table use_info add unique index agd(user_account_id,game_id,daily_date);
alter table user_info add unique key agdkey(user_account_id,game_id,daily_date);

In this way, if When adding the same record to the table, an error message will be returned.

But when used with Insert into…ON DUPLICATE KEY UPDATE…, no error will be reported. If the same record exists, it will be ignored directly.

Example:

 INSERT INTO unit (
    id,
    unitsubclass,
    name,
    state
)
VALUES('1111','CPU','CPU','0' ) ON DUPLICATE KEY UPDATE       unitsubclass=VALUES(unitsubclass),name =VALUES(name),state =VALUES(state)

Another situation is that we need to create this index for the previous table. What should we do if there may be duplicate records in the previous data?

alter ignore table t_aa add unique index(aa,bb);

It will delete duplicate records (one will be retained), and then create a unique index, which is efficient and user-friendly.

Extension extension:

View index:

show index from database table name

alter table database add index index name ( Database field name) PRIMARY KEY (primary key index):

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引);

ALTER TABLE table_name ADD UNIQUE (column) INDEX (normal index):

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

Attachment: How to use commands to create a joint index in mysql

Common joint index

Syntax:

create index index name on table name (field name)

Example:

create index firstIndex on student(id, name, address);

Note:

• Index name , table names, and field names should not be enclosed in quotation marks

• For joint indexes, there can be multiple field names, separated by English commas

• Ordinary index data can be repeated

Result:

Unique joint index

Syntax:

create unique index index name on table name (field name)

Example:

create unique index secondIndex on student(id, name, address);

Note:

• Do not use quotation marks around index names, table names, and field names

• For joint indexes, there can be multiple field names, with the middle Just use English commas to separate them

• The unique index data cannot be repeated

Result:

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