在MySQL中将UUID存储为VARBINARY(16)类型 Store UUID as Binary in MySQL

UUID在sqlserver中可以很方便的存储为uniqueidentifier类型。

在MySQL中将UUID存储为VARBINARY(16)类型效率是比较高的。

存储前去掉UUID中间的“-”连线,字母转换为大写,前补16进制“’0x’”。

I’ve been in search of an efficient way of storing UUIDs in MySQL. From what I’ve read it looks like the best way to do this is by stripping the hyphen and storing it as a VARBINARY in hex form.

Create demo table

 

CREATE TABLE uuid_demo (

id VARBINARY(16)

);

 

Example insert:

 

INSERT INTO uuid_demo

SET id=0x5fa33be57e3409a92b2c00006c63dc25;

-- *Do not wrap hex in quotes.

 

When querying the table for the UUID you will need to use HEX() to get the proper value back.

Example select:

 

SELECT LOWER(HEX(id)) AS id

FROM uuid_demo;

 

Use this to generate your uuid hex:

// Modified from Maciej Strzelecki's script

// http://www.php.net/manual/en/function.uniqid.php#64495

function uuid_hex() {

// version 4 UUID

return '0x'.sprintf(

'%08x%04x%04x%02x%02x%012x',

mt_rand(),

mt_rand(0, 65535),

bindec(substr_replace(

sprintf('%016b', mt_rand(0, 65535)), '0100', 11, 4)

),

bindec(substr_replace(sprintf('%08b', mt_rand(0, 255)), '01', 5, 2)),

mt_rand(0, 255),

mt_rand()

);

}

Sources:
uuid’s as primary keys in mysql
Maciej Strzelecki on uniqid

 

参考:http://labs.ayzenberg.com/2011/11/store-uuid-as-binary-in-mysql/

About 智足者富

http://chenpeng.info

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>