如何删除Laravel中的重复行 - How to Remove Duplicate Rows in Laravel

我需要删除mysql表上指定移动电话号码的重复行。如何使用Laravel查询完成此操作？

如果你想在列"name"中找到重复的值，你也可以这样做：

示例：

$duplicateRecords = DB::select('name')
              ->selectRaw('count(`name`) as `occurences`')
              ->from('users')
              ->groupBy('name')
              ->having('occurences', '>', 1)
              ->get();

然后，您需要循环浏览您的集合并删除项目。

foreach($duplicateRecords as $record) {
    $record->delete();
}

更新Laravel 8

user表中的示例记录：

id	名称
1	卡米拉·奥康纳
2	卡米拉·奥康纳
3	卡米拉·奥康纳
4	Mr。Gussie Dickens IV
5	Patience Jacobs
6	Patience Jacobs

带Eloquent:

App'Model::where('mobile_number', '0123456789')->delete();

使用查询生成器：

DB::table('some_table')->where('mobile_number', '0123456789')->delete();

编辑

以上操作将删除具有mobile_number 0123456789的所有行。如果你想保留一个，请使用这个：

// Get the row you don't want to delete.
$dontDeleteThisRow = App'Model::where('mobile_number', '0123456789')->first();
// Delete all rows except the one we fetched above.
App'Model::where('mobile_number', '0123456789')->where('id', '!=', $dontDeleteThisRow->id)->delete();

要删除重复项但保留第一个或最后一个，请执行以下操作：

// Get all duplicated values. Replace 'table' and 'name' accordingly
$duplicates = DB::table('table') // replace table by the table name where you want to search for duplicated values
              ->select('id', 'name') // name is the column name with duplicated values
              ->whereIn('name', function ($q){
                $q->select('name')
                ->from('table')
                ->groupBy('name')
                ->havingRaw('COUNT(*) > 1');
              })
              ->orderBy('name')
              ->orderBy('id') // keep smaller id (older), to keep biggest id (younger) replace with this ->orderBy('id', 'desc')
              ->get();
        
$value = "";
// loop throuht results and keep first duplicated value
foreach ($duplicates as $duplicate) {
  if($duplicate->name === $value)
  {
    DB::table('table')->where('id', $duplicate->id)->delete(); // comment out this line the first time to check what will be deleted and keeped
    echo "$duplicate->name with id $duplicate->id deleted! 'n";
  }
  else
    echo "$duplicate->name with id $duplicate->id keeped 'n";
  $value = $duplicate->name;
}

如果您想保留每个条目并删除其他重复项。

这是我找到的最简单的方法

$same_data = DB::table('table_name')->where('mobile_number', '0000000000');
if ($same_data->count() > 1) {
    $same_data_before = clone $same_data;
    $top = $same_data->first();
    $same_data_before->where('id', '!=', $top->id)->delete();
}

您也可以尝试映射出条目中的所有电话号码，如果号码再次出现，则将其删除。

例如：

$allContacts = Contact::all()->map->only(['mobile', 'id']);
$uniqueContacts = [];
foreach ($allContacts as $contact) {
    if (in_array($contact['mobile'], $uniqueContacts)) {
        Contact::where('id', $contact['id'])->delete();
    } else {
        array_push($uniqueContacts, $contact['mobile']);
    }
}

$getDuplications = testOrderResults::groupBy('test_id', 'column', 'result')->get();
$idsToKeep = array_column($getDuplications->toArray(), 'id');
testOrderResults::whereNotIn('id', $idsToKeep)->delete();

$getDuplications将数组生成为多组重复项。

$idsToKeep列出了要保留和不删除（OGs）的ID

DB查询删除ID之外的所有条目，以保持

使用Laravel 8，在我的情况下，我的重复项由两列标识，即player_id和played_at

对于每个副本，它保留一条记录（$keeper）并擦除其他副本

ray(sprintf('Game Analyses before: %d', GameAnalysis::count()));
$duplicated = 'DB::table('game_analyses')
    ->select(['player_id', 'played_at', 'DB::raw('count(*) as occurences')])
    ->groupBy(['player_id', 'played_at'])
    ->having('occurences', '>', 1)
    ->get();
ray(sprintf('Found %d duplicate records', GameAnalysis::whereIn('played_at', $duplicated->pluck('played_at'))->count() - $duplicated->count()));
foreach ($duplicated as $duplicateRecord) {
    $keeper = GameAnalysis::where('player_id', $duplicateRecord->player_id)->where('played_at', $duplicateRecord->played_at)->first();
    GameAnalysis::where('id', '<>', $keeper->id)
        ->where('player_id', $duplicateRecord->player_id)
        ->where('played_at', $duplicateRecord->played_at)
        ->delete();
}
ray(sprintf('Game Analyses after: %d', GameAnalysis::count()));

这是我的变体：

    $translations = Translation::query()->orderByDesc('id')->get();
    $deletedRows = 0;
    
    foreach ($translations as $record) {
        $count = $record->where('key', $record->key)->count();
        if($count > 1) {
            while ($count > 1) {
                $record->delete();
                $count--;
            }
            $deletedRows++;
        }
    }
    echo "Duplicate rows:$deletedRows has been deleted'n";
    return 0;

DB::delete('DELETE t1 FROM table_name t1, table_name t2 WHERE t1.id > t2.id AND t1.mobile_number = t2.mobile_number');

用您的表名更改table_name，mobile_number是表中手机号码的列我认为项目很清楚：）

在这里，我正在删除具有相同手机号码的重复用户

$users =  DB::table('users')->get();
    $users  =  $users->groupBy('mobile_no');       
    foreach ($roles as $key => $value){
      if(count($value) >1){
          $id = $value[0]->id;
          $t = $value->where('id','!=',$id)->pluck('id');
          User::whereIn('id',$t)->delete();
      }
    }

public function RemoveDuplicate(){
  
    $car_chars = 'Illuminate'Support'Facades'DB::table('character_diemensions')->get();
    foreach ($car_chars as $char){
        $duplicates = 'App'Models'CharacterDiemension::where(['char_name'=>$char->char_name,'char_type'=>$char->char_type,'board_size'=>$char->board_size,'font_type'=>$char->font_type])->count();
        if($duplicates > 1) {
            $duplicates_records = 'App'Models'CharacterDiemension::where(['char_name'=>$char->char_name,'char_type'=>$char->char_type,'board_size'=>$char->board_size,'font_type'=>$char->font_type])->get();
            foreach ($duplicates_records as $key3 => $duplicates_record){
                if($key3 != 0){
                    $duplicates_record->delete();
                }
            }
        }
    }
    dd('remove duplicate successfully');
};