php - cleaning up db of redundant data

Question

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

can someone give me an sql query to help me clean up this table? after clean up locid(the index) should be resetted, btw this is a county city using this query SELECT locid, country, city FROM location WHERE country = 'US' ORDER BY city ASC . this redundant data came about when i imported an sql text many times through phpmyadmin's import and this is the result,

Answer 1

Add unique index on table location so that no duplicate records will get inserted

ALTER IGNORE TABLE location ADD UNIQUE KEY ix1(country, city);

This will automatically remove duplicate records from the table and for future insert queries you need to use INSERT IGNORE clause to avoid getting duplicate errors.

but as suggested by @AD7six in comments, it might not work on MySQL versions 5.1.41,5.5.1-m2, 6.0: see bug here

or alternate safe way to remove duplicates using DELETE query:

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;

to resettle values of auto_increment column locid, you can just drop the primary key on locid and recreate it:

ALTER TABLE location DROP column locid;
ALTER TABLE location 
      ADD COLUMN locid INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

or alternative way to resettle values of locid using UPDATE query:

SET var_locid = 0;

UPDATE location
SET locid = (@var_locid := @var_locid + 1)
ORDER BY locid ASC;