locid country city 39409 US Aaronsburg 128426 US Aaronsburg 340356 US Aaronsburg 429373 US Aaronsburg 422717 US Abbeville 431344 US Abbeville 433062 US Abbeville 341726 US Abbeville 421248 US Abbeville 40779 US Abbeville 326718 US Abbeville 317654 US Abbeville 16707 US Abbeville 25771 US Abbeville 120301 US Abbeville 132115 US Abbeville 121770 US Abbeville 130397 US Abbeville 5585 US Abbeville 10227 US Abbeville 190173 US Abbeville 491120 US Abbeville 311174 US Abbeville 306532 US Abbeville 164271 US Abbot 465218 US Abbot 58452 US Abbotsford 359399 US Abbotsford 309116 US Abbotsford 8169 US Abbotsford
有人可以给我一个SQL查询来帮助我清理此表吗？在清除locid（索引）后，应该重新设置，这是使用此查询的县城 SELECT locid, country, city FROM location WHERE country = 'US' ORDER BY city ASC 。当我通过phpmyadmin的导入多次导入sql文本时，就会出现这种冗余数据，这就是结果，
SELECT locid, country, city FROM location WHERE country = 'US' ORDER BY city ASC
在表位置添加唯一索引，这样就不会插入重复的记录
ALTER IGNORE TABLE location ADD UNIQUE KEY ix1(country, city);
这将自动从表中删除重复的记录，对于以后的插入查询，您需要使用INSERT IGNORE子句来避免出现重复的错误。
INSERT IGNORE
但正如@AD7six注释中所建议的那样，它可能不适用于MySQL版本5.1.41,5.5.1-m2, 6.0：请参见此处的错误
@AD7six
5.1.41,5.5.1-m2, 6.0
或使用DELETE查询删除重复项的另一种安全方法：
DELETE
DELETE a FROM location a LEFT JOIN ( SELECT locid FROM location GROUP BY country, city )b ON a.locid = b.locid WHERE b.locid IS NULL;
要重新设置auto_incrementcolumn的值，locid只需将primary keyon 放下locid并重新创建它：
auto_increment
locid
primary key
ALTER TABLE location DROP column locid; ALTER TABLE location ADD COLUMN locid INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
或locid使用UPDATE查询重新设置值的替代方法：
UPDATE
SET var_locid = 0; UPDATE location SET locid = (@var_locid := @var_locid + 1) ORDER BY locid ASC;
