php - 更新列，使重复的​​值变得唯一

Question

列title有很多重复的值，不止一次。

我需要更新列，例如如果'gold'重复 - 它变成'gold 1','gold 2'等。

像这样的东西：

$st = $db->query("select id, title from arts order by title asc");
$st->execute();
$x = 0;
while($row = $st->fetch()){
    $title = $row['title'];
    //if($title.is duplicated){
        $x++;
        $title .= ' ' . $x;
        $stb = $db->query("update arts set title = '" . $title . "' where id = " . $row['id']);
        $stb->execute();
    }
}

有什么帮助吗？

标签： phpmysqlsql

Answer 1

这适用于 MySql 5.7：

update arts a inner join (
  select * from (
    select t.id,
      (
        select count(*) + 1 from arts 
        where id < t.id and title = t.title 
      ) counter
    from arts t
  ) t 
) t on t.id = a.id
set a.title = concat(a.title, ' ', t.counter)
where a.title in (
  select h.title from (
    select title from arts
    group by title
    having count(*) > 1    
  ) h 
);

请参阅演示。
对于数据：

| id  | title    |
| --- | -------- |
| 1   | silver   |
| 2   | gold     |
| 3   | diamond  |
| 4   | bronze   |
| 5   | gold     |
| 6   | bronze   |
| 7   | gold     |

结果是

| id  | title    |
| --- | -------- |
| 1   | silver   |
| 2   | gold 1   |
| 3   | diamond  |
| 4   | bronze 1 |
| 5   | gold 2   |
| 6   | bronze 2 |
| 7   | gold 3   |