The method of deleting duplicate Records in mysql
This article mainly explains "the method of deleting duplicate records in mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the method of deleting duplicate records in mysql".
It's a clumsy way to use a temporary transition table.
If the design of the database is not standardized and a table does not have a master key, then there must be duplicate records, or there is such a danger. In oracle, duplicate records can be deleted through rowid. But how do you implement it in mysql?
Google, there is a method as follows:
1. Prepare the base table test to be tested.
Create table test (C1 smallint,c2 smallint)
Insert into test values (1 dint 1)
Insert into test values (1 dint 1)
Insert into test values (1 dint 2)
Insert into test values (2Jing 2)
Insert into test values (2Jing 2)
2. Create a temporary table with the same structure as the original table, but no data.
Create table tmp as select * from test where 1: 2
3. Insert the original table data into the temporary table, and the repeated records are combined into one record.
Insert into tmp select distinct * from test
4. Check the data of the base table and the temporary table.
Mysql > select * from test
+-+ +
| | C1 | c2 |
+-+ +
| | 1 | 1 |
| | 1 | 1 |
| | 1 | 2 |
| | 2 | 2 |
| | 2 | 2 |
+-+ +
5 rows in set (0.00 sec)
Mysql > select * from tmp
+-+ +
| | C1 | c2 |
+-+ +
| | 1 | 1 |
| | 1 | 2 |
| | 2 | 2 |
+-+ +
3 rows in set (0.00 sec)
5. Delete base table data
Mysql > delete from test
Query OK, 5 rows affected (0.02 sec)
6. Insert temporary table data back into base table
Mysql > insert into test select * from tmp
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
7. Check the data of the base table
Mysql > select * from test
+-+ +
| | C1 | c2 |
+-+ +
| | 1 | 1 |
| | 1 | 2 |
| | 2 | 2 |
+-+ +
3 rows in set (0.00 sec)
8. The data is accurate and the duplicate records of the base table are deleted.
Thank you for your reading, the above is the content of "the method of deleting duplicate records in mysql". After the study of this article, I believe you have a deeper understanding of the method of deleting duplicate records in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!
