我正在编译一个包含如下结构/数据的数据库:
id | col_1 | col_2 | col_3 | col_4
-----------------------------------
0 | a | b | c | d
1 | a | b | d | c
2 | a | c | b | d
我想消除重复集,所以在上面的例子中,所有三行都有相同的四个值(显然除了id
),所以我想消除所有重复集。有快速/优雅的方法吗?
我现在正在做的是按字母顺序对每个集合进行排序,保存到数组中,然后比较数组。当你有100行时,这很好,但我知道运行1000行或数百万行的数据集效率不够。。。。
编辑:
预期输出:
id | col_1 | col_2 | col_3 | col_4
-----------------------------------
0 | a | b | c | d
只要只有一个唯一的集合,列或顺序就很重要。mysql UNIQUE约束在这里不起作用,除非我遗漏了什么。。。
SQL Fiddle
这里有一个纯MySQL的答案:
我扩展了你的样本数据:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
col_1 VARCHAR(128),
col_2 VARCHAR(128),
col_3 VARCHAR(128),
col_4 VARCHAR(128)
);
INSERT INTO test VALUES (1,'a','b','c','d'),(2,'a','b','d','c'),(3,'a','d','c','b'),(4,'a','c','d','b'),(5,'e','f','g','h'),(6,'f','h','g','e')
;
SELECT * FROM test;
+----+-------+-------+-------+-------+
| id | col_1 | col_2 | col_3 | col_4 |
+----+-------+-------+-------+-------+
| 1 | a | b | c | d |
| 2 | a | b | d | c |
| 3 | a | d | c | b |
| 4 | a | c | d | b |
| 5 | e | f | g | h |
| 6 | f | h | g | e |
+----+-------+-------+-------+-------+
一个助手表开始发挥作用:
DROP TABLE IF EXISTS tmp_test;
CREATE TABLE tmp_test (id int, col varchar(128));
INSERT INTO tmp_test
SELECT id, col_1 FROM test
UNION
SELECT id, col_2 FROM test
UNION
SELECT id, col_3 FROM test
UNION
SELECT id, col_4 FROM test
;
然后我截断原始表
TRUNCATE TABLE test;
ALTER TABLE test AUTO_INCREMENT = 1;
这样它就可以重新填充"不同"的值:
INSERT INTO test (col_1, col_2, col_3, col_4)
SELECT SUBSTRING_INDEX(gc, '@', 1), SUBSTRING(SUBSTRING_INDEX(gc, '@', 2) FROM LOCATE('@', gc) + 1), SUBSTRING(SUBSTRING_INDEX(gc, '@', 3) FROM LENGTH(SUBSTRING_INDEX(gc, '@', 3))), REVERSE(SUBSTRING_INDEX(REVERSE(gc), '@', 1))
FROM (
SELECT DISTINCT
GROUP_CONCAT(col ORDER BY col SEPARATOR '@') AS gc
FROM tmp_test
GROUP BY id
) sq;
SELECT * FROM test;
+----+-------+-------+-------+-------+
| id | col_1 | col_2 | col_3 | col_4 |
+----+-------+-------+-------+-------+
| 1 | a | b | c | d |
| 2 | e | f | g | h |
+----+-------+-------+-------+-------+
这里有一个PHP解决方案:
$distinct = array();
$duplicates = array();
$stmt = $pdo->query("SELECT id, col_1, col_2, col_3, col_4 FROM my_table");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$id = array_shift($row);
$row = asort($row);
$key = implode('.', $row);
if (isset($distinct[$key])) {
$duplicates[] = $id;
} else {
$distinct[$key] = $id;
}
}
$pdo->beginTransaction();
$idToBeDeleted = 0;
$stmt = $pdo->query("DELETE FROM my_table WEHRE id = :id");
$stmt->bindParam(':id', $idToBeDeleted, PDO::PARAM_INT);
foreach ($duplicates as $duplicate) {
$idToBeDeleted = $duplicate;
$stmt->execute();
}
$pdo->commit();
如果数据库中有许多行,则可以在10.000个删除块之后进行提交。
请注意,此代码未经过测试,可能需要进行一些更改才能正常工作。