“基於分區的計數器重寫”

Question

我使用mysql和php與phpmyadmin。我在基於分區的計數器上遇到了一個主要問題，我想要改進，但是我的sql知識阻止了我這樣做。我對此非常苦惱。

我希望我的表中的重複資料有一個計數器，如果這個值得到一個重複值，它會在值後面加上一個數字，然後從1重新開始，直到遇到一個新值，依此類推。以下是最終結果應該如何顯示：

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

我以前在這裡得到過關於這個計數器的幫助，但它並沒有按照我希望的方式工作。而且，當我按下表單中的插入按鈕後，重新載入phpmyadmin後，表格看起來像這樣：

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

這裡發生了什麼事？我尋求幫助重寫的程式碼是這樣的：

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`

這段程式碼能被重寫成創建一個看起來像第一個表格範例並且在phpmyadmin中正常工作的帶有編號名稱和重複項的表格嗎？非常感謝所有的幫助。請記住，我是一個困擾的中階sql用戶。

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