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

In this issue, the editor will bring you about how to insert duplicate data into MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1 、 insert ignore into

When inserting data, if an error occurs, such as duplicate data, no error will be returned, only in the form of a warning. So make sure there is nothing wrong with the statement itself when using ignore, otherwise it will be ignored. For example:

INSERT IGNORE INTO user (name) VALUES ('telami')

This method is very simple, but there is a possibility that inserting errors not because of duplicate data, but for other reasons, is also ignored.

2 、 on duplicate key update

When primary or unique is repeated, update statements are executed, such as useless statements after update, such as id=id, which have the same function as 1, but the error will not be ignored.

For example, to achieve name duplicate data insertion without reporting errors, you can use the following statement:

INSERT INTO user (name) VALUES ('telami') ON duplicate KEY UPDATE idid = id

A prerequisite for this approach is that the constraint that needs to be inserted needs to be a primary key or a unique constraint (in your business, the only judgment is to set that field to the only constraint, that is, unique key).

3. Insert... Select... Where not exist

According to the conditions of select, we can judge whether to insert or not not only by primary and unique, but also by other conditions. For example:

INSERT INTO user (name) SELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)

This method actually uses a temporary table of mysql, but it uses a subquery, which will have a slight impact on efficiency. If you can use the above, you don't use this.

4 、 replace into

If there is a record with the same primary or unique, delete it first. And insert a new record.

REPLACE INTO user SELECT 1, 'telami' FROM books

This method is that regardless of whether there is the same record or not, it will be deleted and then inserted. In addition, follow the official account Java technology stack, reply in the background: interview, you can get my MySQL interview questions and answers.

Practice

The second way is chosen.

Insert into user (id,username,mobile_number) values (# {item.id}, # {item.username}, # {item.mobileNumber}) ON duplicate KEY UPDATE idid = id

Mybatis is used here, a batch insert operation, mobile_number has added a unique constraint. In this way, when inserting in batches, if the mobile phone number is the same, it will not be inserted again.

This is why duplicate data is not inserted into the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.