[web] How to generate unique numbers?
Author
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.
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.
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.
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.
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
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
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
If running out of numbers is concern, use a bigint for your id.
---
Michael Tanczos
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).
Author
thanks everyone, i figured that using IDENTITY would be the most airtight way.
also, i can't use GUID's because i need numbers only between 0 and 9999...
also, i can't use GUID's because i need numbers only between 0 and 9999...
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
