如何在mysql中将重复行更改为唯一值?
我有一个包含大约 100 万条记录的数据库表.我需要在此表中找到所有重复的名称并使它们唯一.
I have a database table with about 1M records. I need to find all duplicate names in this table and make them unique.
例如...
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 查询或过程执行此操作的有效方法?
Is there an effective way of doing this with a mysql query or procedure?
提前致谢!
推荐答案
这有点棘手.我在本地主机上进行了测试,它可以满足您的要求.如果您有任何问题,请告诉我.SQL FIDDLE
this is a little tricky.. I tested on my local host and it does what you want.. let me know if you have any questions. 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
最终输出如下所示:图片
为了性能,这可能会更好
This may work better for performance sake
1.首先运行此查询
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
我在我的本地测试了它并且它有效,所以你应该能够让它运行:)
I tested this on my local and it works so you should be able to get this to run :)
相关文章