有干净的清理方式在MySQL重复的条目吗?Is there a clean way of cleaning up duplicate entries in MySQL?
In a table, I have three columns - id, name, and count. A good number of name columns are identical (due to the lack of a UNIQUE early on) and I want to fix this. However, the id column is used by other (4 or 5, I think - I would have to check the docs) tables to look up the name and just removing them would break things. So is there a good, clean way of saying "find all identical records and merge them together"?
This kind of question comes up from time to time. No, there's not a really clean way to do it. You have to change all the rows in the child table that depend on unwanted values in the parent table before you can eliminate the unwanted rows in the parent table.
MySQL supports multi-table
DELETE statements (unlike other brands of database) and so you can do some pretty neat tricks like the following:
UPDATE names n1 JOIN names n2 ON (n1.id < n2.id AND n1.name = n2.name) JOIN child_table c ON (n2.id = c.id) SET c.name_id = n1.id ORDER BY n1.id DESC;
Once you have done this on all the child table(s), you can use MySQL's multi-table
DELETE syntax to remove unwanted rows in the parent table:
DELETE FROM n2 USING names n1 JOIN names n2 ON (n1.id < n2.id AND n1.name = n2.name);
Why can't you do something like
update dependent_table set name_id = <id you want to keep> where name_id in ( select id from names where name = 'foo' and id != <id you want to keep>)
- 重装MYSQL(windows)——怎么去旧的数据与新安装吗?Re-installing MYSQL(windows) - How to get to the old data with the new install?
- 做一个mysql表中的ID auto_increment(事后)make an ID in a mysql table auto_increment (after the fact)
- 我如何升级mysql ?How do I upgrade mysql?
- 做数值主键删除记录在数据库中得到重用未来新记录?Do numerical primary keys of deleted records in a database get reused for future new records?
- 加入两个表没有返回的行Joining two tables without returning unwanted row
- 有干净的清理方式在MySQL重复的条目吗?Is there a clean way of cleaning up duplicate entries in MySQL?
- 社会安全号码存储Storing Social Security Numbers
- 为什么不要# 39;t。网络应用程序使用MySQL或允许使用MySQL的刀?Why don't more .NET applications use MySQL or a DAO that allows for the use of MySQL?
- mysql charset cli
- Mysql抵消无限的行Mysql Offset Infinite rows
- 如何同步开发和生产数据库 - How to synchronize development and production database