CODEWITHSUNDEEP
mysqlsqldatabaseuuid
Emil Davtyan
Emil Davtyan

Reputation: 14099

Getting an Unique Identifier without Inserting

I'm looking for the best way to basically get a unique number (guaranteed, not some random string or current time in milliseconds & of a reasonable length about 8 characters) using MySQL (or other ways suggestions welcome).

I just basically want to run some SELECT ... statement and have it always return a unique number with out any inserting into database. Just something that increments some stored value and returns the value and can handle a lot of requests concurrently, without heavy blocking of the application.

I know that I can make something with combinations of random numbers with higher bases (for shorter length), that could make it very unlikely that they overlap, but won't guarantee it.

It just feels like there should be some easy way to get this.

To clarify...

I need this number to be short as it will be part of a URL and it is ok for the query to lock a row for a short period of time. What I was looking for is maybe some command that underhood does something like this ...

LOCK VALUE
RETURN VALUE++
UNLOCK VALUE

Where the VALUE is stored in the database, a MySQL database maybe.

Upvotes: 4

Views: 426

Answers (4)

Stephane Gosselin
Stephane Gosselin

Reputation: 9148

I am not sure I understand exactly, maybe something like this:

SELECT ROUND(RAND() * 123456789) as id

The larger you make the number, the larger your id.

No guarantees about uniqueness of course, this is a quick hack after all and you should implement a check in code to handle the off chance a duplicate is inserted, but maybe this would serve your purpose?

Of course, there are many other approaches possible to do this.

You can easily use most any scripting language to generate this for you, php example here:

//Generates a 32 character identifier that is extremely difficult to predict.
$id = md5(uniqid(rand(), true)); 

//Generates a 32 character identifier that is extremely difficult to predict.
$id = md5(uniqid(rand(), true));

Then use $id in your query or whatever you need your unique id in. In my opinion, the advantage of doing this in a scripting language when interacting with a DB is that it is easier to validate for application / usage purposes and act accordingly. For instance, in your example, whatever method you use, if you wanted to be 100% always sure of data integrity, you have to make sure there are no duplicates of that id elsewhere. This is easier to do in a script than in SQL.

Hope that helps my friend, good-luck!

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26871

Great question.

Shortest answer - that is simply not possible according to your specifications.

Long answer - the closest approach to this is MySQL's UUID but that is neither short, nor is sortable (ie: a former UUID value to be greater/smaller than a previous one).

To UUID or not to UUID? is a nice article describing pros and cons regarding their usage, touching also some of the reasons of why you can't have what you need

Upvotes: 1

Interrobang
Interrobang

Reputation: 17454

You seek UUID().

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

mysql> SELECT UUID();
    -> '6ccd780c-baba-1026-9564-0040f4311e29'

It will return a 128-bit hexadecimal number. You can munge as necessary.

Upvotes: 3

wallyk
wallyk

Reputation: 57804

Is the unique number to be associated with a particular row in a table? If not, why not call rand(): select rand(); The value returned is between zero and one, so scale as desired.

Upvotes: 1

Related Questions