I'm trying to add UUIDs to a couple of tables, but I'm not sure what the best way to store/retrieve these would be. I understand it's far more efficient to use BINARY(16) instead of VARCHAR(36). After doing a bit of research, I also found that you can convert a UUID string to binary with:
UNHEX(REPLACE(UUID(),'-',''))
Pardon my ignorance, but is there an easy way to this with PHP and then turn it back to a string, when needed, for readability?
Also, would it make much difference if I used this as a primary key instead of auto_increment?
EDIT:
Found part of the answer:
$bin = pack("h*", str_replace('-', '', $guid));
How would you unpack it?
In MySQL, you can store UUID values in a compact format ( BINARY ) and display them in human-readable format ( VARCHAR ) with help of the following functions: UUID_TO_BIN. BIN_TO_UUID. IS_UUID.
The UUID_TO_BIN() function is used to convert the UUID values from a human-readable format into a compact format for storing it in the databases. The BIN_TO_UUID() function is used to convert the UUID from the compact format to a human-readable format for displaying.
UUID() function in MySQL. This function in MySQL is used to return a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique Identifier (UUID) URN Namespace”. It is designed as a number that is universally unique.
Okay -- going to try to answer my own question. This is the best I could come up with:
Pack:
$binary = pack("h*", str_replace('-', '', $string));
Unpack
$string = unpack("h*", $binary);
$string = preg_replace("/([0-9a-f]{8})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{12})/", "$1-$2-$3-$4-$5", $string);
Is there any problem with this anyone can see?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With