Jump to content
  • Advertisement
Sign in to follow this  
graveyard filla

[web] How to generate unique numbers?

This topic is 4833 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, Throughout my development I have come into several situations where I need to generate a unique number (unique as in unique forever, always.. e.g. today I generate a different unique number then I will 10 years from now), and I still am not satisfied with any of the ways I have solved this problem... All are done using the database, and work like this 1) Have a table with a single integer field.. make a single insert into this table, setting the field to 1.. then each time you need a unique number, query the table, grab the value, and your unique number is that value + 1... then update that table setting the value to your unique number. (I don't like this because it scares me that 2 or more people use the app at once and I just see this failing in this situation) 2) Have a table with a single field again, this time it's an auto increment field... when you want a unique number, you make an insert into this table, then you do a select max() to grab your unique number.. Again, I don't like this for fear of concurrency issues... 3) Same as step 2, but you use that @@IDENTITY thing that comes built into SQL Server... this seems bullet proof.. but, one question... at one point do you start deleting entries into this table? wont they start stacking and eventually this table will have a thousand records that are all un used? When / what do you do with this data? Anyway, just looking for advice... I just am very unsatisfied with how I generate Unique ID's and I want to solve this problem once and for all..... Thanks for any help.

Share this post


Link to post
Share on other sites
Advertisement
Create two tables. The first table only ever has 1 value in it which is say 63. When your applcation is used to create a unqiue number, query that table to get the number, (in this case 63), then increment the number that is in the table by 1. Your application now has the value 63 and the database has a value of 64.

Now save the 63 value that your application holds in a new table called 'used' or whatever.

Most SQL databases can be queried to see if a table is in use, so that is one way of ensuring two people don't get the same number.

Share this post


Link to post
Share on other sites
Quote:
I don't like this because it scares me that 2 or more people use the app at once and I just see this failing in this situation

Use table read locking.

To prevent problems with tables not getting unlocked, set up a service to handle number retrieval so that multiple application instances aren't trying to update the database directly.

An alternative would be to give each application instance a unique ID and keep its own count. The application instance ID would serve as a namespace so you don't have to worry about synchronizing counts over multiple instances.

Share this post


Link to post
Share on other sites
Not sure which kind of database you are using, for this post I'll assume you are using mysql (please tell me if you aren't!)...

You could always just use the table and store your unique number as a primary key. The main thing about primary keys are that you can't ever have two records in the database with the same primary key. One way to automatically assign the primary key is to set the column to auto_increment.

So like say you have a table called users with user_id, username, and useremail. And you set the user_id as a primary key and have it set to autoincrement as well. When you insert a record into the table, just use a command like:

INSERT INTO users (username, email)
VALUES ('Dan', 'dan@people.com'),
('Bob', 'bob@people.com');

The user_id for "Dan" would be 1, and the user_id for "Bob" would be 2. The really nice thing is since that it is set as a primary key, mysql will return an error if it sees you're going to be making a non-unique user_id. Of course, I could have totally not understand your question! But hope this helps. :D

Share this post


Link to post
Share on other sites
Use method 3. As long as you don't execute another insert over the same connection instance it will return the ID of the last inserted record.

If running out of numbers is concern, use a bigint for your id.

---
Michael Tanczos

Share this post


Link to post
Share on other sites
If it's going to be on the same database, just use IDENTITY, it's what it's meant for. If you need to be unique among multiple databases, or won't always have the same database, or other such things, use "SELECT NEWID() AS UniqueID", which will return a GUID, a 128 bit non-sequential (except on Windows NT 4 and possibly 95) globally unique value, usually expressed in hex form: {F9168C5E-CEB2-4FAA-B6BF-329BF39FA1E4} (if you've used COM you've seen GUIDs).

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!