PHP代码删除除MySQL数据库表中的一条记录之外的所有重复记录-解网

问：

我有一个数据库表，记录了超过 300,000 家公司。

每条记录都有电话号码。我只想从重复的记录中保留一条记录。

例如：

编号	标题	电话
1	公司 01	111 111 11
2	公司 02	222 222 22
3	公司 03	333 333 33
4	公司 04	444 444 44
5	公司 05	333 333 33
6	公司 06	555 555 55
7	公司 07	222 222 22
8	公司 08	333 333 33
9	公司 09	666 666 66

我想保留 id （2， 7）中的任意一条记录和 id （3， 5， 8）中的一条记录

我可以通过 GROUP BY 查询找到重复的记录，但不确定如何删除。

这就是我正在做的

<?php
$qry    = "SELECT phone, COUNT(*) as total FROM companies GROUP BY phone HAVING COUNT(*) > 1"
$result = mysqli_query($dbConn, $qry);
while($row = mysqli_fetch_array($result)) 
{
    // here should be code to keep one record
    
    // here is code to delete duplicate records
    $qry_del    = "DELETE from companies where phone = '".$row['phone']."'";
    mysqli_query($dbConn, $qry_del);
}
?>

PHP 数据库 mysqli

$qry    = "SELECT phone, COUNT(*) as total FROM companies GROUP BY phone HAVING COUNT(*) > 1";

$result = mysqli_query($dbConn, $qry);

while($row = mysqli_fetch_array($result))
{
    // temporary storage for the companies IDs which should deleted
    $recordsToDelete = [];

    // counter that controls how many records are to be deleted
    $maxCountToDelete = $row['total'] - 1;

    // get all companies with specific phone number
    $selectCompanies    = "SELECT id FROM companies WHERE phone = '". $dbConn->real_escape_string( $row['phone'] ) ."'";
    $resultCompanies = mysqli_query($dbConn, $selectCompanies);

    // collect the ids
    $i= 0;
    while($rowCompanie = mysqli_fetch_array($resultCompanies ))
    {
        if( $i < $maxCountToDelete){
            $recordsToDelete[] = $rowCompanie['id'];
        }

        $i++;
    }

    // delete the companies in one SQL statment
    $qryDel    = "DELETE FROM companies WHERE id IN  (". $dbConn->real_escape_string( implode(',', $recordsToDelete) ) .") ";
    

    mysqli_query($dbConn, $qryDel );
}

请注意，“maxCountToDelete”之所以有效，是因为您的删除条件是您希望通过手机选择公司，而不管公司名称是否不同

PHP代码删除除MySQL数据库表中的一条记录之外的所有重复记录

PHP code to delete all duplicate records except One in MySQL database table

评论

评论