php - 如何使用 PHP 或仅使用 MySQL 在 MySQL 中重命名重复项

Question

到目前为止，尝试使用该代码重命名 MySQL 数据库中的重复项，但这只会在名称末尾添加 1。所以如果我有

    UPDATE phpfox_photo n 
  JOIN (SELECT title_url, MIN(photo_id) min_id FROM phpfox_photo GROUP BY title_url HAVING COUNT(*) > 1) d
    ON n.title_url = d.title_url AND n.photo_id <> d.min_id
SET n.title_url = CONCAT(n.title_url, '1'); 


Anna
Anna
Anna

结果是

Anna
Anna1
Anna11

当我得到 200 Annas 结果是 Anna1111111111111111111111111111111111111111111....等

如何在以下公司重命名

Anna
Anna1
Anna2

Answer 1

如果我没有遗漏任何东西，您可以创建一个存储过程，使用游标迭代抛出您的行，如下所示:

DECLARE counter INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE offset INT;
DECLARE title_urlvalue VARCHAR(50);
DECLARE no_more_rows BOOLEAN;
DECLARE ucur CURSOR FOR
  SELECT
    UPDATE phpfox_photo n 
    JOIN (SELECT title_url, MIN(photo_id) min_id 
    FROM phpfox_photo GROUP BY title_url HAVING COUNT(*) > 1) d
    ON n.title_url = d.title_url AND n.photo_id <> d.min_id;
SET offset = 1;
SET no_more_rows = TRUE;
select FOUND_ROWS() into num_rows;
OPEN ucur;
uloop: LOOP
  FETCH  ucur
  if counter >= num_rows then
    no_more_rows = False;
  endif
  INTO   title_urlvalue;
  IF no_more_rows THEN
    CLOSE ucur;
    LEAVE uloop;
   END IF;
   update title_urlvalue = Concat(title_urlvalue,offset);
  SET offset = offset + 1;
  SET counter = counter + 1;
END LOOP uloop;
close ucur;