Substitute for MySQL's UUID Version 1 function?

Question

Context

Web application, PHP 5, MySQL 5.0.91

The Problem

I recently switched from using an auto-incremented integer to a UUID as a primary key for some of my tables. When generating UUID's via MySQL's UUID() function, they are extremely similar to one another:

| uuid                                 |
----------------------------------------
| 1e5988da-afec-11e1-9877-5464f7aa6d24 |
| 408092aa-afad-11e1-9877-5464f7aa6d24 |
  ^------^   ^^
  1      8   11-12

As you can see, only the first 8 characters and the 11th and 12th are different. I understand that UUID Version 1 uses a timestamp and hardware MAC address to generate the UUID. However, I am hesitant in using Version 1 because of these similarities (and the fact that the MAC address will never change, in my case). In addition, if the MAC address never changes, most of the UUID is useless and is wasting space.

My Custom UUID Function

As an experiment, I wrote a custom UUID-generator in PHP:

public static function GenerateUUID()
{
    return
    substr(sha1(Account::GetUsername() . Account::GetUserID()), 18, 8) . "-" .
    substr(md5(time()), rand() % 28, 4) . "-" . 
    substr(md5(date("Y")), rand() % 28, 4) . "-" . 
    substr(sha1(rand()), 20, 4) . "-" . 
    substr(sha1(rand() % PHP_INT_MAX), 17, 12);
}

A sample of the results:

| uuid                                 |
----------------------------------------
| 574d18c2-5080-bac9-5597-45435f363ea1 |
| 574d18c2-30d4-8b5b-4ffd-001744d3d287 |

Here, the first 8 characters are identical for the same user. This was intended, but not needed.

The Question

Is there a preferred/recommended way to generate a Version 4 or Version 5 UUID within a MySQL query?

If not, is it acceptable to generate a custom UUID within PHP (as above) that does not conform to a specification?

Restrictions

• I am using a shared hosting plan with command-line access, but cannot modify the existing MySQL installation.
• I would prefer avoiding third-party packages/libraries.

Notes

• I do not and will not be performing merging, synchronization, or other operations that require a GUID that contains the MAC address. That is not the issue here.

Answer 1

Your concern, that "most of the UUID is useless and is wasting space" is inherent to the size of the data type. You will never be able to have as many entries in your database, as the theoretical limit of 16 bytes allows.

In fact, V1 UUID is more fit than V4 if you use the UUID just as a table ID - because it uses MAC-address and time stamp to prevent clashes. In V4 there is no such mechanism, although practically you don't need to worry too much about clashes either :) You should use V4 UUID instead of V1 if you need your UUID to be unpredictable.

Also, note that composing for example 4x4 byte random values may not be the same as creating a 16 byte random value. As always with crypto and randomness: I would disadvise from implementing your own UUID::V4 routine.

If installed on your machine, you can make use of the php-uuid package.

An example code (which can be used in your application as is) can be found here: http://rommelsantor.com/clog/2012/02/23/generate-uuid-in-php/

Use it like this:

$uuid = uuid_create(1);

Users that are are able to install packages on their webserver, can install the required package, like: (here for ubuntu)

apt-get install php5-dev uuid-dev
pecl install uuid