如果您不想更改列属性,则可以使用下面的查询。
由于您的列具有唯一ID(例如,auto_increment列),因此您可以使用它来删除重复项:DELETE `a`FROM
`jobs` AS `a`,
`jobs` AS `b`WHERE
-- IMPORTANT: Ensures one version remains
-- Change "ID" to your unique column's name
`a`.`ID`
-- Any duplicates you want to check for
AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);
在MySQL中,您可以使用NULL安全相等运算符(也称为“太空飞船运营商”)进一步简化它:DELETE `a`FROM
`jobs` AS `a`,
`jobs` AS `b`WHERE
-- IMPORTANT: Ensures one version remains
-- Change "ID" to your unique column's name
`a`.`ID`
-- Any duplicates you want to check for
AND `a`.`title` <=> `b`.`title`
AND `a`.`company` <=> `b`.`company`
AND `a`.`site_id` <=> `b`.`site_id`;