替代MySQL的UUID版本1功能?

问题描述：

上下文

Web应用程序，PHP 5，MySQL 5.0.91

Web application, PHP 5, MySQL 5.0.91

问题

我最近从使用自动递增的整数切换为UUID作为某些表的主键.通过MySQL的 UUID() 函数生成UUID时，它们彼此极为相似:

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

如您所见，只有前8个字符与第11个和第12个字符不同.我了解UUID版本1使用时间戳和硬件MAC地址来生成UUID.但是，由于这些相似之处(以及MAC地址永远不会更改的事实)，我对使用版本1感到犹豫.另外，如果MAC地址从不改变，则大多数UUID都是无用的，浪费了空间.

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.

我的自定义UUID功能

作为一个实验，我用PHP编写了一个自定义的UUID生成器:

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);
}

结果样本:

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

此处，同一用户的前8个字符相同.这是有意的，但不是必需的.

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

问题

是否存在一种在MySQL查询中生成版本4或版本5 UUID 的首选/推荐方法?

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

如果没有，是否可以在PHP(如上所述)中生成不符合规范的自定义UUID?

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

限制

我正在使用具有命令行访问权限的共享托管计划，但是无法修改现有的MySQL安装.
我宁愿避免使用第三方软件包/库.

注释

我不会也不会执行合并，同步或其他需要包含MAC地址的GUID的操作.这不是这里的问题.

答

您担心的是，大多数UUID都是无用的，浪费了空间"是数据类型的大小所固有的. 您将永远无法在数据库中拥有尽可能多的条目，这是理论上的16字节限制所允许的.

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.

实际上，如果仅将UUID用作表ID，则V1 UUID比V4更合适-因为V1 UUID使用MAC地址和时间戳来防止冲突.在V4中没有这种机制，尽管实际上您也不必太担心冲突:) 如果您需要UUID不可预测，则应使用V4 UUID而不是V1.

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 *es. In V4 there is no such mechanism, although practically you don't need to worry too much about *es either :) You should use V4 UUID instead of V1 if you need your UUID to be unpredictable.

此外，请注意，例如组成4x4字节的随机值可能与创建16字节的随机值不同. 与加密和随机性一样，我不建议您实现自己的UUID :: V4例程.

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.

如果安装在计算机上，则可以使用php-uuid软件包.

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

~~可以在此处找到示例代码(可以在您的应用程序中直接使用): http://rommelsantor.com/clog/2012/02 /23/generate-uuid-in-php/~~

~~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/~~

像这样使用它:

$uuid = uuid_create(1);

能够在其网络服务器上安装软件包的用户可以安装所需的软件包，例如:(此处为ubuntu)

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

替代MySQL的UUID版本1功能?

相关推荐