Laravel中如何删除表内除一条外的同google_maps_city_id重复记录

问题分析 #

你当前的代码会把所有包含重复google_maps_city_id的记录全部删除，核心问题是没有区分“要保留的那条”和“要删除的重复项”——直接通过whereIn匹配所有重复ID后执行删除，自然会把所有相关记录清掉。

检测重复记录 #

先确认哪些google_maps_city_id存在重复，以及重复次数：

$duplicateCheck = DB::table('geo_postal_global')
    ->select('google_maps_city_id', DB::raw('COUNT(*) as duplicate_count'))
    ->whereNotNull('google_maps_city_id')
    ->groupBy('google_maps_city_id')
    ->having('duplicate_count', '>', 1)
    ->get();

// 打印结果查看重复情况
foreach ($duplicateCheck as $item) {
    echo "ID: {$item->google_maps_city_id}，重复次数：{$item->duplicate_count}\n";
}

删除重复记录（保留一条） #

因为表没有主键ID，推荐用窗口函数实现（支持MySQL 8.0+、PostgreSQL等主流数据库），给每个重复组的记录编号，只保留编号为1的那条，删除编号大于1的：

Laravel 执行原生SQL方式 #

// 执行前务必备份数据！先把DELETE换成SELECT g1.*验证要删除的记录是否正确
DB::statement("
    DELETE g1
    FROM geo_postal_global g1
    JOIN (
        SELECT country, city, google_maps_city_id,
               ROW_NUMBER() OVER (PARTITION BY google_maps_city_id ORDER BY (SELECT NULL)) AS rn
        FROM geo_postal_global
    ) g2 ON g1.country = g2.country 
       AND g1.city = g2.city 
       AND g1.google_maps_city_id = g2.google_maps_city_id
    WHERE g2.rn > 1;
");

说明：

• PARTITION BY google_maps_city_id：按google_maps_city_id分组，相同ID的记录归为一组
• ROW_NUMBER() OVER (...)：给每组内的记录编序号
• ORDER BY (SELECT NULL)：随机保留一条记录，如果需要按特定规则（比如country/city排序），可以改成ORDER BY country, city
• 执行前建议将DELETE g1替换为SELECT g1.*，先查看要删除的记录是否符合预期，避免误删

兼容旧版MySQL（5.x）方案 #

如果你的MySQL版本不支持窗口函数，用自连接方式实现：

DB::statement("
    DELETE g1
    FROM geo_postal_global g1
    WHERE EXISTS (
        SELECT 1 FROM geo_postal_global g2
        WHERE g2.google_maps_city_id = g1.google_maps_city_id
        AND (g2.country < g1.country 
             OR (g2.country = g1.country AND g2.city < g1.city))
    )
    AND EXISTS (
        SELECT 1 FROM geo_postal_global g3
        WHERE g3.google_maps_city_id = g1.google_maps_city_id
        GROUP BY g3.google_maps_city_id
        HAVING COUNT(*) > 1
    );
");

注意事项 #

1. 备份优先：删除数据前一定要备份表，或者先运行查询验证要删除的记录
2. 若所有字段（country、city、google_maps_city_id）完全相同，窗口函数方案会随机保留一条，自连接方案会按country/city的字典序保留第一条
3. 原代码中的json_decode($q)和array_unique($t)完全多余，pluck('google_maps_city_id')直接返回的就是去重后的集合，不需要额外处理

内容的提问来源于stack exchange，提问作者mrmar