php - 如何从MySql中删除重复用户但保存原始用户

Question

这个问题已经有答案了:

How to delete duplicates on a MySQL table? (25 个回答)

已关闭 7 年前。

我有一个像这样的表: 我想删除“Ognjen”和“Nikola”的所有重复值，并仅保存这两个值，而不保存它们的重复项。我尝试过这样:

    public function findDuplicate($tabela){
        $query = "SELECT `user_facebook_id` FROM $tabela GROUP BY `user_facebook_id` HAVING count(*) > 1";
        $rez = $this->db->query($query, 3);
        if($rez){
            return $rez;
        }else{
            return false;
        }
    }
$duplicateResult=$settings->findDuplicate($tabela);
if($duplicateResult){
    echo '<div class="alert alert-warning">
  <strong>Warning!</strong> We can see that some of users you inserted already exists in database, so we are authorized to delete them.
</div>
';
    foreach($duplicateResult as $result){
        $to=$result['user_facebook_id'];
        $mysqli1 = new mysqli('localhost','servis_user','QrbRJQK7r52nFpx2','servis_racunara');
        $q="DELETE FROM $tabela WHERE `user_facebook_id`=$to";
        $rez=$mysqli->query($q);
        var_dump($rez);
    }
}

但这只是删除了我表中的所有内容。所以不再有“Ognjen”或“Nikola”了。请帮忙，我被这个问题困扰了。

Answer 1

感谢用户187291；引用:How to delete duplicates on a MySQL table?

“这会删除重复项，而不创建新表

ALTER IGNORE TABLE foobar ADD UNIQUE (title, SID)

注意:只有索引适合内存时才能正常工作”