MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(250),
  fullname VARCHAR(255)
  );
CREATE TABLE users_enumeration (
    username VARCHAR(250),
    id INT AUTO_INCREMENT,
    PRIMARY KEY (username, id)
) ENGINE = MyISAM;
CREATE TRIGGER tr_bi_users_make_fullname
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  DECLARE last_id INT;
  INSERT INTO users_enumeration (username) 
    VALUES (NEW.username);
  SET last_id := LAST_INSERT_ID();
  DELETE FROM users_enumeration
    WHERE username = NEW.username AND id < last_id;
  SET NEW.fullname = CONCAT_WS('#', NEW.username, LPAD(last_id, 4, 0));
END
INSERT INTO users (id, username) VALUES 
(1,'Exuser1'),
(2,'Exuser2'),
(3,'Exuser3'),
(4,'Exuser2'),
(5,'Exuser2');
SELECT * FROM users;

Records: 5  Duplicates: 0  Warnings: 0




id
username
fullname


1
Exuser1
Exuser1#0001

2
Exuser2
Exuser2#0001

3
Exuser3
Exuser3#0001

4
Exuser2
Exuser2#0002

5
Exuser2
Exuser2#0003

id	username	fullname
1	Exuser1	Exuser1#0001
2	Exuser2	Exuser2#0001
3	Exuser3	Exuser3#0001
4	Exuser2	Exuser2#0002
5	Exuser2	Exuser2#0003