如何在"mysql repetitive values"后添加"ID"或"count number"

Question

我有一个大数据库，NAME表上有很多重复值无法手动更新它们。我试图制作一个程序来添加ID的最后一个值，并使表具有唯一性，但我失败了。

如何使用php或mysql更新它们？

这是一个包含重复值的表的示例。有4个亚历克斯，2个沙玛和1个珍妮的名字

| ID | NAME     |
|  1 | alex     |
|  2 | alex     |
|  3 | shama    |
|  4 | jenny    |
|  5 | alex     |
|  6 | shama    |
|  7 | alex     |

这就是我想要做的

包括重复名称后面带点的ID

| ID | NAME     |
|  1 | alex     |
|  2 | alex.2   |
|  3 | shama    |
|  4 | jenny    |
|  5 | alex.3   |
|  6 | shama.2  |
|  7 | alex.4   |

Answer 1

假设表中不包含以句点+数字结尾的名称，则可以使用以下UPDATE查询：

UPDATE
  names INNER JOIN
  (SELECT   n1.id, n1.name, count(n2.id)+1 cnt
   FROM     names n1 INNER JOIN names n2
            ON n1.name=n2.name AND n1.id>n2.id
   GROUP BY n1.id, n1.name) s
  ON names.id = s.id
SET
  names.name = CONCAT(names.name, '.', s.cnt)

请看这儿的小提琴。

编辑

如上所述，只有在没有以句点和数字结尾的记录的情况下，此解决方案才会起作用，例如，如果您有这些记录，它将失败：

alex
alex
alex.2

而且，您只能使用此解决方案来修复您的记录一次。如果你需要插入新记录，你不能再次启动这个查询，但你应该使用这样的insert查询：

INSERT INTO names (ID, Name)
SELECT  8,
        CONCAT('alex.',
          COALESCE(MAX(CAST(SUBSTR(Name, LENGTH('alex.')+1, 1) AS UNSIGNED)) + 1,'')
        ) AS Name
FROM    names
WHERE   Name RLIKE '^alex.[0-9]*$'

（这是从这个答案中提取的一个经过轻微修改和固定的查询）。

即使这让事情变得稍微复杂一点，我认为计算一个计数器比公开一个内部ID要好得多（一个名为alex.2的帐户看起来比一个名alex.78964的帐户好得多…）

菲德尔来了。