MySQL 8.0

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