如何将重复的行更改为mysql中的唯一值?
问题描述:
我有一个约1M记录的数据库表。我需要在此表中找到所有重复的名称,并使其唯一。如何将重复的行更改为mysql中的唯一值?
例如...
Id Name
-----------
1 A
2 A
3 B
4 C
5 C
应改为...
Id Name
-----------
1 A-1
2 A-2
3 B
4 C-1
5 C-2
是否与一个MySQL查询或过程这样做的有效途径?
在此先感谢!
答
这是一个有点棘手。我测试了我的本地主机上,它你想要做什么..让我知道,如果你有任何问题。 SQL FIDDLE
UPDATE temp t1,
(
SELECT
id as unique_id,
new_name
FROM(
SELECT
id,
IF(@ROW = Name, @COUNT, @COUNT := 1),
CONCAT(Name, ' - ', @COUNT) AS new_name,
@ROW := Name,
@COUNT := @COUNT + 1
FROM temp
JOIN (SELECT @COUNT := 0, @ROW := "") AS t
WHERE Name IN(SELECT Name FROM temp
GROUP BY Name
HAVING COUNT(Name) > 1)
) AS temp_test
) as testing
SET t1.Name = testing.new_name where t1.id = testing.unique_id
最终输出结果如下:PICTURE
编辑: 这可以更好地为性能考虑
先运行此查询
SET SESSION group_concat_max_len = 1000000; -- longer if needed
SET @query1 := (
SELECT
GROUP_CONCAT(DISTINCT unique_name)
FROM temp
JOIN(
select Name as unique_name
FROM temp
GROUP BY name
HAVING COUNT(Name) > 1
) as t
);
2.然后运行此更新
UPDATE temp t1,
(
SELECT
id as unique_id,
new_name
FROM(
SELECT
id,
IF(@ROW = Name, @COUNT, @COUNT := 1),
CONCAT(Name, ' - ', @COUNT) AS new_name,
@ROW := Name,
@COUNT := @COUNT + 1
FROM temp
JOIN (SELECT @COUNT := 0, @ROW := "") AS t
WHERE FIND_IN_SET (`name`, @query1)
) AS temp_test
) as testing
SET t1.Name = testing.new_name where t1.id = testing.unique_id
我测试了我的地方,它的工作原理,所以你应该能够得到这个运行:)
答
UPDATE table_x AS upd
SET upd.Name = CONCAT(upd.Name, '-', upd.Id)
WHERE upd.id IN(
SELECT sel.id
FROM table_x AS sel
WHERE sel.Name = upd.Name
AND sel.Id != upd.Id
);
+2
对不起,我认为我忘了提及为安全起见,新的唯一名称可能不包含该id。该名称必须附加一个从1开始的连续编号,用于每组重复项。 – gunner1095
答
首先,你应该保存的副本ID在临时桌子。
Drop temporary table if not exist temp;
Create temporary table temp (
Select max(id)'id' from table_x group by Name having count(*)>1
);
Delete from table_x as x,temp as t where x.id = t.id;
只是这样做反复... ...之后,设置独特的键名称字段U将得到唯一的行..
谢谢你,但它太痛苦了(测试没有更新,只是选择,10k数据集超过10分钟),这方面还有改进的余地吗?还请包括“UPDATE”语法 – Sharky
@Sharky对不起,我以为我已经包含更新语法..我只是做了。从我所能想到的事情来做这件特定的事情,这将会和其他任何方法一样快......因为它只是一次更新表格就不会那么糟糕。这意味着它运行一次来更改数据库,所以如果它需要比希望的时间长一点,那应该没问题。我会着眼于试图优化查询..如果你可以发布一个查询的解释,所以我们可以看到你如何索引,这将有助于非常感谢 –
感谢您的答复,生病明天看看并接受:D是的,我也不相信有空间因为对于每一个更新的行都必须进行新的选择。索引存在于id上,并且不存在于名称上。所以选择速度很快,并且更新不会重建任何索引。 – Sharky