如何在mysql中将重复行更改为唯一值? [英] How to change duplicate rows to unique values in 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 :)

这篇关于如何在mysql中将重复行更改为唯一值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！