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

This article mainly introduces how to re-MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

Demand analysis

There are duplicate records in the database, delete and retain one of them (whether to repeat the benchmark is multiple fields)

Solution

When you meet this need, you probably have a train of thought in mind. The quickest thing I can think of is that I can solve it through a sql statement, but I am too shallow for complex sql statements, so I want to ask the boss for help.

Get help.

Because this demand is a little anxious, so the first thought is that we can find peers in this field to solve the problem, and then share this problem with colleagues. As a result, this product casually Baidu, threw me a never used sql sentence, let me try it myself, in my heart ten thousand things galloping past.

Own Baidu

A sql statement was found:

DELETEFROM vitae aWHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count (*) > 1) AND rowid NOT IN (SELECT min (rowid) FROM vitae GROUP BY peopleId, seq HAVING count (*) > 1)

This statement was found in the article [remove duplicate data and keep only one entry in MySQL]. This sql train of thought is obvious and has the following three steps:

SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count (*) > 1 query duplicate records in the table as a condition

SELECT min (rowid) FROM vitae GROUP BY peopleId, seq HAVING count (*) > 1 query shows that the minimum value of ID in duplicate records in the table is the second condition

Finally, according to the above two conditions, delete the other duplicate records except the minimum ID in the duplicate records.

But sadly, there was an error running this statement, roughly meaning that the table could not be updated at the same time as the query.

Code resolution

Based on the above sql statement, it is thought that it may be possible to achieve the same goal in two steps in code:

Fetch the duplicate dataset first

Delete the rest of the duplicate data in a loop based on the queried dataset

I have an idea, and I can write it very quickly, but when I run it, it takes me about 116s, and then I think I must find a sql statement that I can use, post the code and run the result:

Perfect [to redeem] SQL

Finally, I got the perfect answer in a technology group. Take a look at this sql statement:

DELETE consum_recordFROM consum_record, (SELECT min (id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count (*) > 1) t2WHERE consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_timeAND consum_record.id > t2.id

If you take a closer look at the above sql sentence, it is not difficult to figure out the train of thought, which is probably divided into three steps:

(SELECT min (id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count (*) > 1) T2 queries duplicate records to form a set (temporary table T2), in which is the minimum ID of each duplicate record

Consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_time is associated with the field that determines the duplicate datum

Delete records in the original table whose id is greater than id in T2 according to the condition

When I saw this sentence, I thought to myself that it was too powerful. It is surprising that such a simple sql statement can solve such a complex problem.

Running is also super fast, the original code cycle execution, need about 116s, but here 0.3s is fine, awesome ~

Thank you for reading this article carefully. I hope the article "how to re-MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.