This is the 23rd day of my participation in the August More Text Challenge

There are five types of Mysql indexes:

INDEX: a basic INDEX with no restrictions. UNIQUE: The INDEX column must have UNIQUE values, but empty values are allowed. PRIMARY: A special unique index that does not allow null values. FULLTEXT index (FULLTEXT) : can be used for MyISAM tables, mysql5.6 can also be used for innodb tables, used for retrieving text information in an article, for large data, the generation of FULLTEXT index is time-consuming and space. Join (composite) indexes: To improve mysql efficiency, you can create composite indexes, following the “leftmost prefix” principle.

So let’s look at the unique index.

Earlier we looked at a primary key index, which is a special kind of unique index. The difference is that a primary key index cannot have a null value, but a unique index can have a null value.

One: Basic use

SQL > create index (); SQL > create index ();

Create a single column unique index

alter table sc add unique (name);
Create a unique index for multiple columns

alter table sc add unique key `name_score` (`name`,`score`);
Copy the code

Select * from unique index where id = 1;

alter table sc drop index name;

Insert the code slice hereCopy the code

Two: unique index function

1: The biggest use is to ensure that the data written to the database is unique.

A single column unique value is basically a primary key.

Commonly used are generally multi-column unique index, such as: current goods, a user can only buy one item. We set the user ID and commodity ID columns as unique indexes. So you can avoid a user to repeat the purchase situation.

Example:

Create table;

MariaDB [test]> CREATE TABLE t(
    -> c1 CHAR(1) not null,
    -> c2 CHAR(1) not null,
    -> c3 CHAR(1) not null,
    -> c4 CHAR(1) not null,
    -> c5 CHAR(1) not null
    -> )ENGINE myisam CHARSET UTF8;
Query OK, 0 rows affected (0.09 sec)
Copy the code

Add a few pieces of data:

MariaDB [test]> insert into t VALUES('1'.'1'.'1'.'1'.'1'), ('2'.'2'.'2'.'2'.'2'), ('3'.'3'.'3'.'3'.'3'), ('4'.'4'.'4'.'4'.'4'), ('5'.'5'.'5'.'5'.'5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
Copy the code

Create unique index:

MariaDB [test]> alter table t add unique key `name_score` (`c2`,`c3`);
Query OK, 5 rows affected (0.01 sec)              
Records: 5  Duplicates: 0  Warnings: 0
Copy the code

Failed to write duplicate data:

MariaDB [test]> insert into t (c1,c2,c3,c4,c5) values ('1'.'1'.'1'.'1'.'1');
ERROR 1062 (23000): Duplicate entry 1-1 ' ' for key 'name_score'
Copy the code

2: Improves the query speed

When index is used, the query usually takes about 2 seconds, or even 5 seconds when busy

With unique, the query time of 0.0003 seconds is negligible

Third: unique index and primary key index specific difference

1: Null values are allowed in columns of the uniqueness constraint, but not in columns of the primary key constraint. 2: The uniqueness constraint can be placed on one or more columns, and these columns or combinations of columns must be unique. However, the column where the uniqueness constraint resides is not the primary key column of the table. 3: The uniqueness constraint forces the creation of a unique index on the specified column. By default, unique non-clustered indexes are created, but you can also specify that the indexes created are clustered indexes. 5. A table has at most one primary key, but can have many unique keys

Four: Avoid policies when unique key conflicts exist

1: Use the INSERT ignore statement

Insert Ignore ignores data already in the database (by primary key or unique index), inserts new data if there is no data in the database, and skips data if there is data.

MariaDB [test]> insert ignore into t (c1,c2,c3,c4,c5) values ('1'.'1'.'1'.'1'.'2'); Query OK, 0 rows affected (0.00 SEC) MariaDB [test]> select * from t; +----+----+----+----+----+ | c1 | c2 | c3 | c4 | c5 | +----+----+----+----+----+ | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 |  2 | | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | +----+----+----+----+----+ 5 rowsin set (0.00 sec)
Copy the code

Sql executed successfully, but we queried the table data and found that the write was not successful. The only index already exists. Skipped the write command.

2: Use the replace into statement

Replace into first tries to insert data into the table. If the table already contains row data (based on primary key or unique index), delete row data and insert new data; otherwise, insert new data directly.

To use replace into, you must have delete and INSERT permissions

Example:

MariaDB [test]> replace into t (c1,c2,c3,c4,c5) values ('1'.'1'.'1'.'1'.'2');
Query OK, 2 rows affected (0.00 sec)
Copy the code
Delete before write
 
MariaDB [test]> select * from t; +----+----+----+----+----+ | c1 | c2 | c3 | c4 | c5 | +----+----+----+----+----+ | 1 | 1 | 1 | 1 | 2 | | 2 | 2 | 2 | 2 |  2 | | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | +----+----+----+----+----+ 5 rowsin set (0.00 sec)
Copy the code

3: Use the INSERT on Duplicate key update statement

If you specify on duplicate key UPDATE at the end of the INSERT into statement and insert rows that result in duplicate values in a UNIQUE index or PRIMARY key, perform update on the row where duplicate values occur. If it does not cause duplicate problems, insert a new row, just like normal INSERT into.

To use insert into, you must have insert and UPDATE permissions

If a new record is inserted, the value of the affected row displays 1; If the original record is updated, the value of the affected row displays 2; If the value is the same before and after the record is updated, the value for the number of affected rows displays 0

Example:

MariaDB [test]> insert into t (c1,c2,c3,c4,c5) values ('1'.'1'.'1'.'1'.'2') on duplicate key update c2 = 5;
Query OK, 2 rows affected (0.00 sec)
 
MariaDB [test]> select * from t; +----+----+----+----+----+ | c1 | c2 | c3 | c4 | c5 | +----+----+----+----+----+ | 1 | 5 | 1 | 1 | 2 | | 2 | 2 | 2 | 2 |  2 | | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | +----+----+----+----+----+ 5 rowsin set (0.00 sec)
Copy the code

Here are a few things to note:

insert … Innodb on duplicate key (S) : innoDB on duplicate key (S) : INNODB on duplicate key (S) : INNODB on duplicate key (S) : InnoDB An X(exclusive lock) is then added to the record and an update write is performed.

If two transactions concurrently execute the same statement, a death lock will occur.

Therefore, try to avoid:

Try to use this statement for tables with multiple unique keys

Do not use this statement in the case of insert contents that may have concurrent transactions

4:

All three methods avoid insert failures caused by duplicate primary keys or unique indexes.

Insert Ignore Can ignore duplicate data and insert only non-duplicate data.

Replace into and insert… On duplicate key update (on duplicate key update) is used to replace existing duplicate data. Replace into (on duplicate key update) is used to replace existing rows and insert new rows. insert … On Duplicate Key update If duplicate rows are encountered, the system directly updates the original rows. How to update the fields depends on the statement after the update.

For good suggestions, please enter your comments below.

Welcome to my blog guanchao.site

Welcome to applets: