MySql:根据列重复值删除表行? [英] MySql: remove table rows depending on column duplicate values?
问题描述
那么如何删除那些具有重复年值的行?
谢谢
我想你可以简单地尝试使用IGNORE添加一个独特的索引: p>
ALTER IGNORE TABLE`table` ADD UNIQUE INDEX`name`(`column`);
MySQL应该回应如下:
查询确定,受影响的4524行(1.09秒)
记录:4524重复:9342警告:0
当然,你会把它留给MySQL来决定哪些行被删除。
编辑:
这适用于您喜欢的列数:
ALTER IGNORE TABLE` table` ADD UNIQUE INDEX`name`(`col1`,`col2`,`col3`);
检查MySQL的文档 CREATE INDEX 。一个常见的问题(至少有一次我碰到一次)是忘记 NULL = NULL
不是真的(但$ code> NULL ),因此对于两列的UNIQUE索引,允许{42,NULL}和{42,NULL}。
I have a table with year column and this column shouldn't have duplicate values. So I end up with a table with only one 2007 year record for example.
So how could I delete those rows that have duplicate year value?
Thanks
I think you could simply try adding a UNIQUE INDEX using IGNORE:
ALTER IGNORE TABLE `table` ADD UNIQUE INDEX `name` (`column`);
MySQL should respond with something like:
Query OK, 4524 rows affected (1.09 sec)
Records: 4524 Duplicates: 9342 Warnings: 0
Of course, you'll leave it up to MySQL to decide which rows to drop.
EDIT:
this works for as many columns as you like:
ALTER IGNORE TABLE `table` ADD UNIQUE INDEX `name` (`col1`, `col2`, `col3`);
check MySQL's documentation on CREATE INDEX. A common gotcha (at least one I ran into once) is to forget that NULL = NULL
isn't true (but NULL
), hence {42, NULL} and {42, NULL} are allowed for a UNIQUE index on two columns.
这篇关于MySql:根据列重复值删除表行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!