„Partitionsbasiertes Umschreiben von Zählern“

Question

Ich verwende MySQL und PHP mit phpmyadmin. Ich habe ein großes Problem mit partitionsbasierten Zählern, das ich gerne verbessern würde, aber meine SQL-Kenntnisse hindern mich daran. Ich bin darüber sehr beunruhigt.

Ich hätte gerne einen Zähler für doppelte Daten in meiner Tabelle. Wenn der Wert ein Duplikat erhält, fügt er nach dem Wert eine Zahl hinzu und beginnt von vorne bei 1, bis er auf einen neuen Wert trifft und so weiter. So sollte das Endergebnis aussehen:

---------------------------
1  | Josh-1
---------------------------
2  | Josh-2
--------------------------
3  | Josh-3 
--------------------------
4  | Josh-4 
--------------------------
5  | Fred-1 
--------------------------
6  | Fred-2
--------------------------
7  | Fred-3
-------------------------

Ich habe hier schon einmal Hilfe zu diesem Zähler bekommen, aber er hat nicht so funktioniert, wie ich es wollte. Und wenn ich nach dem Neuladen von phpmyadmin auf die Schaltfläche „Einfügen“ im Formular drücke, sieht das Formular so aus:

---------------------------
1  | Josh-1-1-1
---------------------------
2  | Josh-2
--------------------------
3  | Josh-3 
--------------------------
4  | Josh-4 
--------------------------
5  | Fred-1 
--------------------------
6  | Fred-2
--------------------------
7  | Fred
-------------------------

Was ist hier los? Der Code, den ich um Hilfe beim Umschreiben bitte, ist dieser:

UPDATE usermeta u1,
(SELECT
u1.`id`, CONCAT(u1.`name`,'-',ROW_NUMBER() OVER(PARTITION BY u1.`name` ORDER BY u1.`id`)) newname
FROM 
usermeta u1 JOIN (SELECT `name` , COUNT(*) FROM usermeta GROUP BY `name` HAVING COUNT(*) > 1) u2
ON u1.`name` = u2.`name` ) u3
SET u1.`name` = u3.`newname`
WHERE u1.`id` = u3.`id`

Kann dieser Code umgeschrieben werden, um eine Tabelle mit nummerierten Namen und Duplikaten zu erstellen, die wie das erste Tabellenbeispiel aussieht und in phpmyadmin einwandfrei funktioniert? Jede Hilfe wird sehr geschätzt. Bitte bedenken Sie, dass ich ein erfahrener SQL-Benutzer mit Problemen bin.

Answer 1

Possible solution - BEFORE INSERT trigger and additional MyISAM table with secondary autoincrement:

Working table

Additional table

Trigger

Insert rows - the AI index is added to the name. Check the result.

Look what is stored in additional table now.

db<>fiddle here

---

If your working table user exists already, and it contains some data, then you'd create additional table and fill it with data using, for example,

CREATE TABLE user_index (id INT AUTO_INCREMENT,
                         name VARCHAR(127),
                         PRIMARY KEY (name, id)) ENGINE=MyISAM
SELECT MAX(SUBSTRING_INDEX(name, '-', -1) + 0) id,
       SUBSTRING_INDEX(name, '-', 1) name
FROM user
GROUP BY 2;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=38f028cfe1c9e85188ab0454463dcd78