在MySQL数据库中,我有行。例如:
Varchar1 | Varchar2
XXXXXXXXXX YYYYYYYYYY
YYYYYYYYYY XXXXXXXXXX
XXXXXXXXXX CCCCCCCCCC
YYYYYYYYYY AAAAAAAAAA
CCCCCCCCCC AAAAAAAAAA
如何在第 1 行和第 2 行中找到(和删除)重复?
不应删除一行(1 或 2)。
SELECT t1.id, t1.varchar1,t1.varchar2,t2.id, t2.varchar1,t2.varchar2
from yourtable t1, yourtable t2
where t1.id<>t2.id and t1.varchar1=t2.varchar2
试试这个,如果不起作用,请回来:)
如果这检索到您想要的数据,那么删除非常简单:
DELETE from yourtable WHERE id in( SELECT t1.id from yourtable t1, yourtable t2
where t1.id<>t2.id and t1.varchar1=t2.varchar2)
警告:仅当您不在生产环境中,或者您确定自己正在做什么或您在事务中时,才使用 DELETE(开始...
解决了。我用 varchar1 和 varchar2 的哈希的按位 AND 添加了 BIGINT 值。
ALTER TABLE t1 ADD COLUMN hex1 BIGINT UNSIGNED NOT NULL;
ALTER TABLE t1 ADD COLUMN hex2 BIGINT UNSIGNED NOT NULL;
ALTER TABLE t1 ADD COLUMN hex BIGINT UNSIGNED NOT NULL;
UPDATE t1 SET
hex1 = CONV(SUBSTRING(CAST(SHA(varchar1) AS CHAR), 1, 16), 16, 10),
hex2 = CONV(SUBSTRING(CAST(SHA(varchar2) AS CHAR), 1, 16), 16, 10),
hex = hex1&hex2;
ALTER TABLE t1 DROP COLUMN hex1;
ALTER TABLE t1 DROP COLUMN hex2;
和按十六进制列分组
SELECT t1.* GROUP BY hex;