Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use MySQL to add a federated unique index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Joint unique index

A federated unique index is required in the project:

For example, there are the following requirements: each person can only produce one record each day: in addition to the program agreement, the database itself can also be set:

For example: there are two aa,bb fields in the t_aa table. If you do not want to have two identical records (that is, the value of the aa field can be duplicated; the value of the bb field can also be duplicated, but the combined value of a record (aa,bb) cannot be duplicated), 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, an error message is returned if the same record is added to the table.

But with Insert into... ON DUPLICATE KEY UPDATE... If you use it, you will not report an error, there is the same record, just ignore it.

Example:

INSERT INTO unit (id, unitsubclass, name, state) VALUES ('1111) name = VALUES (name), state = VALUES (state)

There is also a situation where we need to create this index for the previous table, and it is possible that there are duplicate records in the previous data.

Alter ignore table t_aa add unique index (aa,bb)

It deletes duplicate records (retains one) and then creates a unique index, efficient and user-friendly.

Extend extension:

View the 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 (unique index)

ALTER TABLE table_name ADD UNIQUE (column) INDEX (General 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`) attached: how to use commands to create federated indexes in mysql

General federated index

Syntax:

Create index index name on table name (field name)

Example:

Create index firstIndex on student (id, name, address)

Note:

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

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

General index data can be repeated

Results:

Unique federated index

Syntax:

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

Example:

Create unique index secondIndex on student (id, name, address)

Note:

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

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

Unique index data is not repeatable

Results:

This is the end of the content of "how to add a federated unique index using MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.