• notice
  • Congratulations on the launch of the Sought Tech site

MySQL batch insertion encounters a unique index avoid method

One.Background

In the past, when using SQL Server for table partitioning, I encountered many problems about unique indexes: Step8: SQL Server When table partitioning encounters unique constraints, I did not expect to encounter such problems in MySQL partitioning: MySQL Table partitioning in action.

Today we come to understand some knowledge of MySQL unique index: including how to create, how to insert in batches, and some skills on SQL;

Where is the source of these problems? What do you have in common? There is also the concept of partition alignment in MySQL? Unique index is a requirement that appears in many systems.Is there any way to avoid it? How much does it affect performance?

Second,Process

(1) Import difference data, ignore duplicate data, use IGNORE INTO

When MySQL creates a table, we usually create a table with an auto-increment ID value as the primary key, then MySQL will use PRIMARY KEY as the clustered index key and primary key.Since it is the primary key, it is of course unique , So repeated execution of the insert statement below will report 1062 error: as shown in Figure1;

The code is as follows:

--Create 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,'aa',1),(2,102,'bbb',2),(3,103, 'ccc',3);

u1_1062

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

But in the actual production environment, the requirement is often to set a unique index in the UserId key value.Today I will use this as an example to test the unique index:

The 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,'aa',1),(2,102,'bbb',2),(3,103,'ccc', 3);

--Insert test data 2
INSERT INTO testtable2(Id,UserId,UserName,UserType)
VALUES(1,201,'aa a',1),(2,202,'bbb',2),(3,203,'ccc',3),(4,101,'xxxx',5);

u2_table1

(Figure2: testtable1 record)

u3_table2

(Figure3: testtable2 record)

By executing the above SQL script, we created a unique index on both testtable1 and testtable2: UNIQUE KEY `IX_UserId` (`UserId`), which means that UserId is unique in both testtable1 and testtable2 tables.If you put testtable2 Import the data into testtable1 in batches.If you execute the SQL below [Import 1], an error of 1062 will occur, causing the whole process to be rolled back, failing to achieve the purpose of importing differential data.

The code is as follows:

INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

u4_unique

(Figure4: Duplicate entry '101' for key'IX_UserId')

MySQL provides a keyword: IGNORE, this keyword determines whether each record exists, whether it violates the unique index in the hungry table, if it exists, it will not be inserted, and the non-existent record will be inserted.

The code is as follows:

--Import 2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

So after executing [Import 2], the result of Figure 5 will be produced, which has achieved our goal Yes, but have you found that the auto-incremented ID value skipped some values? This is because we failed to execute [Import 1] before.Although our transaction is rolled back, the auto-increment ID will have a fault.There are also such problems in SQL Server.Extended reading: Simple and practical SQL script Part: Finding SQL Server self-increasing ID value discontinuous records

u5_effect

(Figure5: IGNORE effect)

(2) Import and overwrite duplicate data, use of REPLACE INTO

1.Roll back testtable1 and testtable2 to F respectivelyThe status of igure2 and Figure3 (named with TRUNCATE TABLE and then execute Insert statement).At this time, execute the following SQL to see what the effect is:

The code is as follows:

--Import 3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;

u6_rep

(Figure6: REPLACE effect)

From Figure 6 above, we can see that the record with UserId of 101 has changed, not only UserName has been modified, but UserType has also become NULL.

So, if a duplicate is found during import, delete it and insert it.If the record has multiple fields, if some fields are not assigned when inserting, then these fields of the newly inserted record will be empty (the newly inserted record UserType is NULL).

It should be noted that when you replace, if the inserted table does not specify a column, it will be represented by NULL instead of the original content of the table.If the inserted content column is the same as the inserted table column, NULL will not appear.

2.If the UserType field of our table structure is not allowed to be empty, and there is no default value, what will happen if we execute [Import 3]?

u7_not null

(Figure7: Return warning message)

u8_0

(Figure8: UserType is set to 0)

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

3.If we want to update the value of the UserType field when importing, this is naturally very simple.Use the following SQL script to solve it:

The code is as follows :

--Import 4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

u9_rep

(Figure9: Update UserType together)

(3) Import reserved duplicate data unspecified fields,INSERT INTO ON DUPLICATE KEY UPDATE Use of

Roll back testtable1 and testtable2 to the state of Figure2 and Figure3 respectively (use TRUNCATE TABLE to name and then execute the Insert statement).At this time, execute the following SQL to see what the effect is:

The code is as follows:

--Import 5
INSERT INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName=testtable2.UserName;

u10_update

(Figure10: Keep UserType value)

Comparing Figure2, Figure3 and Figure10, Record with UserId of 101: Updated UserName Value, the value of UserType is retained; but because UserType is not specified in [Import 5], the UserType of the newly inserted record is NULL.

The 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;

u11_update

(Figure11: Keep UserType value)

Compare Figure2, Figure3 and Figure11, only insert the UserId and UserName fields of the testtable2 table, but keep the UserType field of the testtable1 table.If duplicate records are found, update operations; on the basis of the original records, update the contents of the specified fields, and keep the contents of other fields.

(4) Summary

When inserting a record containing duplicate values ​​on a UNIQUE key, the default insert will report 1062 error.MYSQL can be processed in the above three different ways and your business logic.

Three.References

Several ways to insert MYSQL to deal with duplicate key values

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+