4

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!

|improve this question
  • 1
    You could do this with an insert trigger, though it may not be the best idea. – Aaron Brown Jul 14 '12 at 12:58
  • Thank you for your reply. I've never heard of "insert trigger", but for what i red, it seems it could work. Why however, would it not be the best idea? – Jeffrey Jul 14 '12 at 13:00
  • MyISAM is not transactional. – ypercubeᵀᴹ Jul 14 '12 at 13:01
  • What i mean is that currently PHP handles the insert. which means that 2 PHP scripts can be executed at the same time, and both call the next autoincrement, which is for both the same... – Jeffrey Jul 14 '12 at 13:04
  • If you have high insert rate that occurs simultaneously, you may consider a GUID key instead of an int. – NoChance Jul 14 '12 at 13:13
4

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 ;
|improve this answer
  • but, using this i would have to preform a recursive check in PHP to find out if id = 1 with the username... Beside that, id on itself wont contain a unique number anymore which i can use to select a row. (which makes linking to the table more dificult than necessary) @update:<br/> "Simplicity is best...". That actually will do! If however the user want to change his username in a later stadium, i can just allow him to set a value in another column "name" and use that if it contains anything... Thank you very much! – Jeffrey Jul 14 '12 at 13:12
  • Recursive check? Why? Anyway, I think I misread your question, see the update. – ypercubeᵀᴹ Jul 14 '12 at 13:13
  • Update will work for me! One question thought: how to make this work: SELECT if(empty(name)){ id AS name}else{ name AS name} FROM test WHERE id = someID – Jeffrey Jul 14 '12 at 13:19
  • With "empty", you mean an empty string?: '' – ypercubeᵀᴹ Jul 14 '12 at 13:21
  • empy means empty string indead. I just found the answear to my own question. (SELECT id, IF(name = "", id, name) AS name FROM test WHERE id = 1). Thank you very much for yout help! – Jeffrey Jul 14 '12 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.