By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(127));
CREATE TABLE user_index (id INT AUTO_INCREMENT,
name VARCHAR(127),
PRIMARY KEY (name, id)) ENGINE=MyISAM;
CREATE TRIGGER insert_user_index
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
DECLARE new_index INT;
INSERT INTO user_index (name) VALUES (NEW.name);
SET new_index = LAST_INSERT_ID();
DELETE FROM user_index WHERE name = NEW.name AND id < new_index;
SET NEW.name = CONCAT_WS('-', NEW.name, new_index);
END
INSERT INTO user (name) VALUES
('Josh'),
('Josh'),
('Fred'),
('Josh'),
('Fred'),
('Fred'),
('Josh');
SELECT * FROM user;
| id | name |
|---|---|
| 1 | Josh-1 |
| 2 | Josh-2 |
| 3 | Fred-1 |
| 4 | Josh-3 |
| 5 | Fred-2 |
| 6 | Fred-3 |
| 7 | Josh-4 |
SELECT * FROM user_index;
| id | name |
|---|---|
| 3 | Fred |
| 4 | Josh |
DROP TABLE user_index;
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;
SELECT * FROM user_index;
| id | name |
|---|---|
| 3 | Fred |
| 4 | Josh |
INSERT INTO user (name) VALUES
('Josh'),
('Josh'),
('Fred');
SELECT * FROM user;
| id | name |
|---|---|
| 1 | Josh-1 |
| 2 | Josh-2 |
| 3 | Fred-1 |
| 4 | Josh-3 |
| 5 | Fred-2 |
| 6 | Fred-3 |
| 7 | Josh-4 |
| 8 | Josh-5 |
| 9 | Josh-6 |
| 10 | Fred-4 |