"Partition-based counter rewriting"

Question

I use mysql and php with phpmyadmin. I have a major problem with partition based counters that I would like to improve, but my SQL knowledge prevents me from doing so. I'm very distressed by this.

I would like to have a counter for duplicate data in my table, if this value gets a duplicate value it will add a number after the value and start over from 1 until it encounters a new value and so on. Here's how the final result should look:

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

I've gotten help here before with this counter, but it didn't work the way I wanted it to. And, when I press the insert button in the form, after reloading phpmyadmin, the form looks like this:

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

What's going on here? The code I'm asking for help rewriting is this:

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`

Can this code be rewritten to create a table with numbered names and duplicates that looks like the first table example and works fine in phpmyadmin? All help is greatly appreciated. Please keep in mind that I am a troubled intermediate sql user.

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