PHP/MySQL: Storing and retrieving UUIDS

Question

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?

Answer 1

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?

Answer 2

For php >= 5.4 we can use hex2bin and bin2hex:

$binary = hex2bin(str_replace('-', '', $value));


$string = bin2hex($value);
$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);

Or using function for more organization:

function uuid2bin($uuid) {
   return hex2bin(str_replace('-', '', $uuid));
}

function bin2uuid($value) {
   $string = bin2hex($value);

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

For Laravel (using ID field):

public function setIdAttribute($value)
{
   $this->attributes[ 'id' ] = hex2bin(str_replace('-', '', $value));
}

public function getIdAttribute($value)
{
   $string = bin2hex($value);

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

Answer 3

As @Johan mentioned, you need to use uppercase H (Hex string, high nibble first) in order to be compatible with MySQL HEX / UNHEX functions

function uuid_to_bin($uuid){
  return pack("H*", str_replace('-', '', $uuid));
}

A more compact decode back to UUID function, using only unpack() and join() methods. Attention: You need to name the unpack array parameters / keys in order not to be overwritten !

function bin_to_uuid($bin){
  return join("-", unpack("H8time_low/H4time_mid/H4time_hi/H4clock_seq_hi/H12clock_seq_low", $bin));
}

And for older MySQL versions, where the uuid_to_bin() and bin_to_uuid() functions are missing:

DELIMITER $$
CREATE FUNCTION `fn_uuid_to_bin`(`s` CHAR(36)) RETURNS binary(16)
    DETERMINISTIC

RETURN UNHEX(REPLACE(s, '-', ''))$$

DELIMITER ;

DELIMITER $$
CREATE FUNCTION `fn_bin_to_uuid`(`b` BINARY(16)) RETURNS char(36) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
  DECLARE hex CHAR(32);
  SET hex = HEX(b);
  RETURN LOWER(CONCAT(LEFT(hex, 8), '-', MID(hex, 9,4), '-', MID(hex, 13,4), '-', MID(hex, 17,4), '-', RIGHT(hex, 12)));
END$$

DELIMITER ;