Autoincrement field on duplicate username

Question

On registration i am asking a username for a game. But like usual in games the username is already used. To prevent overload by trying multiple usernames i want to add a system like discord have.

Exusername #0001

Exusername #0002

To keep it simple i dont want to add more than 4 numbers after name.

How can i make a autoincrement field which is connected to the usename? Only if a user name is already used it should increment by one, else it starts by 1 withouth making a first fetch (there will be millions of rows). Or can you give me better advise to solve my issue ?

For example registration timeline:

Exuser1 -> 0001
Exuser2 -> 0001
Exuser3 -> 0001
Exuser2 -> 0002
Exuser2 -> 0003

@ErgestBasha Both is fine, a new user could be registered with Exuser2#0002 or Exuser2#0004, doesnt matter. It will rarely happen because accounts dont get deleted when the app is removed. — user5441400, Sep 28 '22 at 08:12
*How can i make a autoincrement field which is connected to the usename?* The most simple solution uses MyISAM table with secondary autoincremented column. — Akina, Sep 28 '22 at 09:30

Akina · Accepted Answer · 2022-09-28T10:27:07.537

CREATE TABLE users (
    username VARCHAR(250),
    id INT AUTO_INCREMENT,
    PRIMARY KEY (username, id)
) ENGINE = MyISAM;

https://dbfiddle.uk/NCnMAV6p

i already have primary key, which is a unique identifier provided by google – user5441400

If so then (trigger + additional table) seems to be the only solution.

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

https://dbfiddle.uk/R-YMICE4

I forgot to add that i already have primary key, which is a unique identifier provided by google and im not sure joining a second table only to display name might cause a little overhead aswell. — user5441400, Sep 28 '22 at 09:44

Autoincrement field on duplicate username

1 Answers1