我有几千个联系人的数据库,并希望删除所有重复的记录.我目前的SQL查询运行良好(在记录中 – 电话,电子邮件,名称1重复).查询删除具有较低ID但最后发生记录的重复项.但在某些情况下,记录的另一个字段已经填写(重要的是标题和名称2).我想要实现的是mysql检查这些字段是否被填写并仅保留记录了大部分信息的记录.
我的查询
<?php
$del_duplicate_contacts = $mysqli->query("
DELETE ca
FROM contacts ca
LEFT JOIN
(
SELECT MAX(id) id, name1, tel, email
FROM contacts
GROUP BY name1, tel, email
) cb ON ca.id = cb.id AND
ca.name1 = cb.name1 AND
ca.tel = cb.tel AND
ca.email = cb.email
WHERE cb.id IS NULL
");
?>
表格示例:
ID title name1 name2 tel email
1 John 01234 1@1.com
2 Mr John Smith 01234 1@1.com
3 John 01234 1@1.com
我的查询将删除记录1和2.我想只保留nr 2并删除1和3.
我怎么能做到这一点?有可能吗?或者也许我应该涉及PHP,如果是这样如何?
解决方法:
在group_concat中使用order by,你可以试试这个:
DELETE c1 FROM contacts c1
JOIN (
SELECT
substring_index(group_concat(id ORDER BY ((title IS NULL OR title ='') AND (name2 IS NULL OR name2 = '')), id DESC), ',', 1) AS id,
name1, tel, email
FROM contacts
GROUP BY name1, tel, email
) c2
ON c1.name1 = c2.name1 AND c1.tel = c2.tel AND c1.email = c2.email AND c1.id <> c2.id;