Скачать или смотреть How to Effectively Delete Duplicate Records in a MySQL Database Using PHP

Learn how to easily delete duplicate records in a MySQL database table with a simple PHP script that retains one unique record for each duplicate.
---
This video is based on the question https://stackoverflow.com/q/74590382/ asked by the user 'Xpert Solutions' ( https://stackoverflow.com/u/20614195/ ) and on the answer https://stackoverflow.com/a/74590946/ provided by the user 'user141080' ( https://stackoverflow.com/u/9671934/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PHP code to delete all duplicate records except One in MySQL database table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Deleting Duplicate Records from a MySQL Database Table

Managing a large database can often lead to challenges, especially when it comes to handling duplicate records. If you're working with a MySQL database containing hundreds of thousands of entries, like company records with phone numbers, you might find yourself needing to delete duplicate records while preserving at least one instance of each unique entry. This guide will guide you through a straightforward PHP solution to achieve this.

The Problem

Imagine your database table, companies, has over 300,000 records. Each record contains details about different companies, including a unique phone number. However, many of these records have duplicate phone numbers, and you want to keep only one record for each phone number while removing the rest. For instance, given the following records:

idTitlePhone1Company 01111 111 112Company 02222 222 223Company 03333 333 334Company 04444 444 445Company 05333 333 336Company 06555 555 557Company 07222 222 228Company 08333 333 339Company 09666 666 66You want to keep just one record for phone numbers like 222 222 22 and 333 333 33 and delete the rest.

The Solution

To remove duplicates from the MySQL database while keeping one record, you can utilize the following PHP script. The script first identifies the duplicates and then deletes them based on their identifiers while preserving one record.

Step-by-Step Breakdown

Query to Find Duplicates: Use a SQL GROUP BY query to find duplicate phone numbers.

Gather IDs to Delete: For each duplicate phone number, collect the IDs of the duplicate entries that you want to remove.

Delete Duplicates: Execute a DELETE statement to remove the selected duplicate records from the database.

PHP Script Example

Here’s a complete PHP script that accomplishes this task:

[[See Video to Reveal this Text or Code Snippet]]

Important Notes

Performance Considerations: This approach is straightforward but might not be the most efficient for very large datasets. Ensure your database can handle the load and test the script on a smaller dataset first.

Backup Your Data: Always back up your database before performing delete operations, as these changes are irreversible.

Conclusion

By following the steps outlined above, you’ll be able to efficiently clean up your database by removing duplicate records while retaining at least one instance of each unique entry. Implementing this script will help you maintain the integrity of your data and ensure that your database remains organized. Happy coding!