User autoincrement ID for username

Question

I want to automatically create user's, which all have a unique ID.

Now here's the problem. If two users are created at exactly the same nanosecond, it may not cause problems. Thus, what i do now is:

1. Get the next Autoincrement number
2. Add a new user with the name "U" + autoincrement number + random(0-9)

When more than 10 users are created at the exact same nanosecond, this will still cause problems (in theory!). Thus, I'm looking for a way to let MySQL handle the username.

Consider the following table:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I want to use:

INSERT INTO test (id, name) VALUES (null, "U" + id)

where id is the autoincrement number. But, since id is not defined in MySQL, it will contain 0.

How to define id, so that it will contain the autoincrement number of that row. This can easily be done by two queries, but I'm looking to do this in only one query :)

Thanks!

Answer 1

With MyISAM, there's a neat trick you can do:

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  PRIMARY KEY (name, id)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 
  AUTO_INCREMENT=1 ;

With the above PRIMARY KEY, if all users have different name, they will be given id = 1. If another user comes and chooses name that already exists, he will get id=2, etc.

---

UPDATE

It seems I misread your question and you only need to have name = 'U243' for user with id=243. If that's the case, you don't have to store the name at all. Use this (myISAM or InnoDB, it will work for both):

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 
  AUTO_INCREMENT=1 ;

and then, to retrieve the id and name:

SELECT 
    id
  , 'U' || id  AS name
FROM
    test ;