清理数据库中的冗余数据 [英] cleaning up db of redundant data
问题描述
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文本时,就产生了这种冗余数据,这就是结果,
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,
推荐答案
在表位置添加唯一索引,这样就不会插入重复的记录
Add unique index on table location so that no duplicate records will get inserted
ALTER IGNORE TABLE location ADD UNIQUE KEY ix1(country, city);
这将自动从表中删除重复的记录,对于以后的插入查询,您需要使用INSERT IGNORE
子句,以避免出现重复的错误.
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.
,但正如@AD7six
在注释中所建议的那样,它可能不适用于MySQL版本5.1.41,5.5.1-m2, 6.0
:请参见bug 此处
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
或使用DELETE
查询删除重复项的另一种安全方法:
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;
要重新设置auto_increment
列locid
的值,只需将primary key
放在locid
上并重新创建它即可:
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;
或使用UPDATE
查询重新设置locid
值的替代方法:
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;
这篇关于清理数据库中的冗余数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!