Storing MySQL GUID/UUIDs
This is the best way I could come up with to convert a MySQL GUID/UUID generated by UUID() to a binary(16):
UNHEX(REPLACE(UUID(),'-',''))
And then storing it in a BINARY(16)
Are there any implications of doing it this way that I should know of?
Not many implications. It will slow down the queries a little, but you will hardly notice it.
UNIQUEIDENTIFIER
is stored as 16-byte binary
internally anyway.
If you are going to load the binary into a client and parse it there, note the bit order
, it may have other string representation than the initial NEWID()
.
Oracle
's SYS_GUID()
function is prone to this issue, converting it to a string gives different results on client and on server.
From MySQL 8.0 and above you could use UUID_TO_BIN:
UUID_TO_BIN(string_uuid) , UUID_TO_BIN(string_uuid, swap_flag)
Converts a string UUID to a binary UUID and returns the result. (The IS_UUID() function description lists the permitted string UUID formats.) The return binary UUID is a VARBINARY(16) value.
CREATE TABLE t (id binary(16) PRIMARY KEY);
INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true));
INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true));
INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true));
SELECT *, BIN_TO_UUID(id) FROM t;
DB-Fiddle.com Demo
I would hash it into a 8-byte integer and store the integer using a low-collision high-efficiency one-way hash algorithm like MurmurHash64A. This uses a lot less space and can be indexed and/or partitioned on. There is a SourceForge project that includes MemCached functions for mySQL (http://forge.mysql.com/projects/project.php?id=250) which might include MurmurHash64A, since Memchached uses it, but I don't know. Or look at this implementation of FNV for mySQL: http://www.xaprb.com/blog/2008/03/09/a-very-fast-fnv-hash-function-for-mysql/
链接地址: http://www.djcxy.com/p/16264.html上一篇: UUID有多独特?
下一篇: 存储MySQL GUID / UUID