5 Replies - 1822 Views - Last Post: 15 September 2011 - 04:54 AM Rate Topic: -----

#1 nathanpc  Icon User is offline

  • SCIENCE!
  • member icon

Reputation: 112
  • View blog
  • Posts: 1,171
  • Joined: 31-July 09

Best Way To Generate a Unique Random Key In a SQL Database

Posted 14 September 2011 - 07:06 PM

I'm creating an application in PHP that will need to generate a random unique ID to insert in a varchar(40), which of course needs to contain a 40 characters (case sensitive, numbers and letters). I need it to be unique each time it needs to be regenerated, so which is the best way to do this without overload the server?

PS: I'm thinking big, because I hope my service becomes popular :)

Is This A Good Question/Topic? 0
  • +

Replies To: Best Way To Generate a Unique Random Key In a SQL Database

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2913
  • View blog
  • Posts: 10,089
  • Joined: 08-August 08

Re: Best Way To Generate a Unique Random Key In a SQL Database

Posted 14 September 2011 - 07:10 PM

Why does it need to be random? What's wrong with unique and auto-incrementing?
Was This Post Helpful? 1
  • +
  • -

#3 nathanpc  Icon User is offline

  • SCIENCE!
  • member icon

Reputation: 112
  • View blog
  • Posts: 1,171
  • Joined: 31-July 09

Re: Best Way To Generate a Unique Random Key In a SQL Database

Posted 14 September 2011 - 07:26 PM

Randomization gives more security in my thoughts. Correct me if I'm wrong (I need a learn more SQL & Security)
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2913
  • View blog
  • Posts: 10,089
  • Joined: 08-August 08

Re: Best Way To Generate a Unique Random Key In a SQL Database

Posted 14 September 2011 - 07:49 PM

From the user's perspective, if they ever see their id number it will appear to be random. You should have a means of determining if they belong to the id number presented in any case.
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Best Way To Generate a Unique Random Key In a SQL Database

Posted 14 September 2011 - 10:35 PM

I agree that a unique auto-incrementer is sufficient for this case. If you really want a long random looking string, you can always use UUID:
SELECT uuid();


UUID creates practically unique identifiers (as opposed to guaranteed unique). The chances of creating two identical UUIDs is next to nothing (according to Wikipedia for every 3.6 quintillion you create, there's a 50% chance that just one duplicate will have been created).

Another thing that you should probably consider is that a JOIN on int is a lot quicker than a JOIN on varchar. I can't provide an online reference for this, but at my work, I was given the task to optimise a bunch of report queries. The best and easiest optimisation I found was matching a user against UserID rather than UserName - it sped up all the affected queries by a factor of 4 (i.e. - 2 seconds as opposed to 8 seconds)
Was This Post Helpful? 4
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6053
  • View blog
  • Posts: 23,488
  • Joined: 23-August 08

Re: Best Way To Generate a Unique Random Key In a SQL Database

Posted 15 September 2011 - 04:54 AM

View Postnathanpc, on 14 September 2011 - 10:26 PM, said:

Randomization gives more security in my thoughts. Correct me if I'm wrong (I need a learn more SQL & Security)


Security through obscurity is never a good thing.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1