删除除了MySQL中的行之外的所有重复行?(复制)
Possible Duplicate:
Remove duplicate rows in MySQL可能的重复:删除MySQL中的重复行。
How would I delete all duplicate data from a MySQL Table?
如何从MySQL表中删除所有重复的数据?
For example, with the following data:
例如,下列数据:
SELECT * FROM names;
+----+--------+
| id | name |
+----+--------+
| 1 | google |
| 2 | yahoo |
| 3 | msn |
| 4 | google |
| 5 | google |
| 6 | yahoo |
+----+--------+
I would use SELECT DISTINCT name FROM names;
if it were a SELECT
query.
我会从名字中选择不同的名字;如果是SELECT查询。
How would I do this with DELETE
to only remove duplicates and keep just one record of each?
我如何使用DELETE来删除重复项并只保留一个记录?
2 个解决方案
#1
833
NB - You need to do this first on a test copy of your table!
NB -您需要首先在您的表的测试副本上执行此操作!
When I did it, I found that unless I also included AND n1.id <> n2.id
, it deleted every row in the table.
当我做的时候,我发现除非我也包括n1。id < > n2。id,它删除了表中的每一行。
1) If you want to keep the row with the lowest id
value:
1)如果要保持id值最小的行:
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name
2) If you want to keep the row with the highest id
value:
2)如果要保持id值最高的行:
DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name
I used this method in MySQL 5.1
我在MySQL 5.1中使用了这种方法
Not sure about other versions.
不确定其他版本。
Update: Since people Googling for removing duplicates end up here
Although the OP's question is about DELETE, please be advised that using INSERT and DISTINCT is much faster. For a database with 8 million rows, the below query took 13 minutes, while using DELETE, it took more than 2 hours and yet didn't complete.
更新:虽然OP的问题是关于删除,但是由于人们在google上搜索删除重复,所以请注意使用INSERT和DISTINCT会更快。对于一个有800万行的数据库,下面的查询需要13分钟,而使用DELETE时,需要2个多小时,但还没有完成。
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
SELECT DISTINCT cellId,attributeId,entityRowId,value
FROM tableName;
更多相关文章
- Python爬虫数据存储MySQL【1】连接方式
- 如何用sql语言只获得数据库当前日期,且格式为"yyyy-mm-dd"?
- python数据存储系列教程——python中mysql数据库操作:连接、增删
- Python3.6实现scrapy框架爬取数据并将数据插入MySQL与存入文档中
- 优惠券使用限制的数据库设计
- mysql数据库的主从同步过程详述
- mysql 数据库备份和还原 实践
- 工作日记(连接SQL Sever 2005服务器数据库)
- docker应用-6(mysql+mycat 搭建数据库集群)