How do I rename duplicates in MySQL using PHP or j

问题:

trying to rename duplicates in MySQL database so far using that code but this only adding 1 at the end of name. So if I have

    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

Result is

Anna
Anna1
Anna11

When I got 200 Annas result is Anna1111111111111111111111111111111111111111111....etc

how do I do it to rename in the following inc

Anna
Anna1
Anna2

回答1:

if i didn't miss something you can make a stored procedure that iterates throw your rows using cursors to do that as following:

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;

回答2:

With User-Defined Variables

SET @counter:=0;
SET @title_url:='';
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 = IF(n.title_url <> @title_url, CONCAT(@title_url:=n.title_url, @counter:=1), CONCAT(n.title_url, @counter:=@counter+1)); 

回答3:

Maybe you can use modulo to produce numbering, like this (SQLite example, but should be similar in mysql):

SELECT *, (rowid  % (SELECT COUNT(*) FROM table as t WHERE t.name = table.name ) ) FROM table ORDER BY name

All you need is to translate rowid and modulo function, both availible in mysql.

Then you can CONCAT results as you desire.

回答4:

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 = 
              CASE
                     WHEN <last char is int>
                     THEN <replace last char with incremented last char>
                     ELSE <string + 1>
              END