Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHP/MySQL: Storing and retrieving UUIDS

Tags:

php

uuid

mysql

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?

like image 834
Greg Avatar asked May 15 '10 05:05

Greg


People also ask

How does MySQL store UUID?

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.

How do you save a database 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.

Does MySQL supports UUID?

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.


1 Answers

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?

like image 148
Greg Avatar answered Oct 12 '22 09:10

Greg