How to Avoid unique indexes during MySQL batch insertion

I,Background

In the past, when I used SQL Server to partition a table, I encountered a lot of questions about unique indexes: Step 8: SQL Server. When the table partition encounters a unique constraint, I did not expect this problem to occur in MySQL partitions: MySQL table partitions.

Today, we will learn about the unique index of MySQL, including how to create and batch insert, and some tips on SQL;

What are the root causes of these problems? What do they have in common? What is the concept of partition alignment in MySQL? The unique index is required in many systems. What measures can be taken to avoid it? How much impact does it have on performance?

II,Process

(1) Import differential data and IGNORE duplicate data. ignore into usage

When creating a table in MySQL, we usually create a table with an auto-incrementing ID value as the primary key. MySQL uses the primary key as the clustered index KEY and primary key. Since it is a primary key, of course it is unique, so repeated execution of the following insert statement will report the 1062 error, as shown in Figure1;
Copy codeThe Code is as follows:
-- Create a test table
Create table 'testtable '(
'Id' INT (11) unsigned not null AUTO_INCREMENT,
'Userid' INT (11) default null,
'Username' VARCHAR (10) default null,
'Usertype' INT (11) default null,
Primary key ('id ')
) ENGINE = innodb default charset = utf8;

-- Insert Test Data
Insert into testtable (Id, UserId, UserName, UserType)
VALUES (1,101, 'A', 1), (2,102, 'bbb ', 2), (3,103, 'ccc', 3 );

(Figure1: Duplicate entry '1' for key 'primary ')

However, in the actual production environment, it is often necessary to set a unique index in the UserId key value. Today I will use this as an example to test the unique index:
Copy codeThe Code is as follows:
-- Create test table 1
Create table 'testtable1 '(
'Id' INT (11) unsigned not null AUTO_INCREMENT,
'Userid' INT (11) default null,
'Username' VARCHAR (10) default null,
'Usertype' INT (11) default null,
Primary key ('id '),
Unique key 'ix _ userid' ('userid ')
) ENGINE = innodb default charset = utf8;

-- Create test table 2
Create table 'testtable2 '(
'Id' INT (11) unsigned not null AUTO_INCREMENT,
'Userid' INT (11) default null,
'Username' VARCHAR (10) default null,
'Usertype' INT (11) default null,
Primary key ('id '),
Unique key 'ix _ userid' ('userid ')
) ENGINE = innodb default charset = utf8;

-- Insert test data 1
Insert into testtable1 (Id, UserId, UserName, UserType)
VALUES (1,101, 'A', 1), (2,102, 'bbb ', 2), (3,103, 'ccc', 3 );

-- Insert Test Data 2
Insert into testtable2 (Id, UserId, UserName, UserType)
VALUES (1,201, 'aaa', 1), (2,202, 'bbb ', 2), (3,203, 'ccc', 3), (4,101, 'xxxx ', 5 );

(Figure2:Testtable1Record)

(Figure3: testtable2 record)

By executing the preceding SQL script, we created a UNIQUE index in both testtable1 and testtable2: UNIQUE KEY 'ix _ userid' ('userid '), this indicates that UserId is unique in tables testtable1 and testtable2. If you batch import data from testtable2 to testtable1, If you execute the following SQL statement [import 1], error 1062 will occur, as a resultRollback, Does not achieve the purpose of importing differential data.
Copy codeThe Code is as follows:
Insert into testtable1 (UserId, UserName, UserType)
SELECT UserId, UserName, UserType FROM testtable2;

(Figure4: Duplicate entry '000000' for key 'ix _ userid ')

MySQL provides a keyword: IGNORE, which determines whether each record exists and whether it violates the unique index in the ELE. Me table. If it exists, it will not be inserted, but it will be inserted if it does not exist.
Copy codeThe Code is as follows:
-- Import 2
Insert ignore into testtable1 (UserId, UserName, UserType)
SELECT UserId, UserName, UserType FROM testtable2;

Therefore, after [import 2] is executed, the result of Figure5 is generated, which has achieved our goal. However, you have not found that the auto-increment ID value skips some values, this is because we failed to execute [import 1]. Although our transaction is rolled back, the auto-increment ID will be faulty. This problem also occurs in SQL Server. Extended reading: simple and practical SQL script Part: Searching for Discontinuous records of auto-increment ID values of SQL Server

(Figure5: IGNORE effect)

(2) import and overwrite duplicate data, replace into usage

1. Roll Back testtable1 and testtable2 to the statuses of Figure2 and Figure3 respectively (use the truncate table name and then execute the Insert statement). Then, execute the following SQL statement to check the effect:
Copy codeThe Code is as follows:
-- Import 3
Replace into testtable1 (UserId, UserName)
SELECT UserId, UserName FROM testtable2;

(Figure6: REPLACE effect)

From Figure6, we can see that the record with UserId 101 has changed, not only the UserName has been modified, but also the UserType has changed to NULL.

Therefore, if duplicate fields are found in the import process, delete them first and then insert them. If there are multiple fields in the record, if some fields are not assigned a value during insertion, the fields of the newly inserted record are NULL (the UserType of the newly inserted record is NULL ).

Note that when you replace a table, if the inserted Table does not specify a column, it will be expressed as NULL, rather than the original content of the table. If the inserted content column is the same as the inserted Table column, no NULL occurs.

2. If the UserType field in our table structure cannot be blank and there is no default value, what will happen when we execute [Import 3?

(Figure7: Return warning information)

(Figure8: UserType is set to 0)

Through Figure7 and Figure8, we know that the data record is still inserted, but the Field 'usertype' doesn' t have a default value warning is returned, the UserType field of the inserted record is set to 0 ('usertype' is the int data type ).

3. If we want to update the value of the UserType field together during the import, this is naturally very simple. We can solve the problem by using the following SQL script:
Copy codeThe Code is as follows:
-- Import 4
Replace into testtable1 (UserId, UserName, UserType)
SELECT UserId, UserName, UserType FROM testtable2;

(Figure9: Update UserType together)

(3) fields not specified for the imported and retained duplicate data,INSERT INTO ON DUPLICATE KEY UPDATEUse

Roll back testtable1 and testtable2 to the statuses of Figure2 and Figure3 respectively (use the truncate table name and then execute the Insert statement). Then, execute the following SQL statement to check the effect:
Copy codeThe Code is as follows:
-- Import 5
Insert into testtable1 (UserId, UserName)
SELECT UserId, UserName FROM testtable2
ON DUPLICATE KEY UPDATE
Testtable1.UserName = testtable2.UserName;

(Figure10: Reserved UserType value)

Comparison between Figure2, Figure3, and Figure10,Record with UserId 101: the UserName value is updated and the UserType value is retained. However, because UserType is not specified in [import 5], the UserType of the newly inserted record is NULL.
Copy codeThe Code is as follows:
-- Import 6
Insert into testtable1 (UserId, UserName, UserType)
SELECT UserId, UserName, UserType FROM testtable2
ON DUPLICATE KEY UPDATE
Testtable1.UserName = testtable2.UserName;

(Figure11: Reserved UserType value)

Compared with Figure2, Figure3, and Figure11, only the UserId and UserName fields in Table testtable2 are inserted, but the UserType fields in Table testtable1 are retained. If a duplicate record is found, update the record. Based on the original record, update the content of the specified field, and retain the content of other fields.

(Iv) Summary

When a record containing duplicate values is inserted on a UNIQUE key, the default insert error 1062 is returned. MYSQL can process the record in three different ways than your business logic.

III,References

MYSQL insert several methods for processing duplicate key values