How should I store GUID in MySQL tables?

Do I use varchar(36) or are there any better ways to do it?

Solution 1:

My DBA asked me when I asked about the best way to store GUIDs for my objects why I needed to store 16 bytes when I could do the same thing in 4 bytes with an Integer. Since he put that challenge out there to me I thought now was a good time to mention it. That being said...

You can store a guid as a CHAR(16) binary if you want to make the most optimal use of storage space.

Solution 2:

I would store it as a char(36).

Solution 3:

Adding to the answer by ThaBadDawg, use these handy functions (thanks to a wiser collegue of mine) to get from 36 length string back to a byte array of 16.

DELIMITER $$

CREATE FUNCTION `GuidToBinary`(
    $Data VARCHAR(36)
) RETURNS binary(16)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result BINARY(16) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Data = REPLACE($Data,'-','');
        SET $Result =
            CONCAT( UNHEX(SUBSTRING($Data,7,2)), UNHEX(SUBSTRING($Data,5,2)),
                    UNHEX(SUBSTRING($Data,3,2)), UNHEX(SUBSTRING($Data,1,2)),
                    UNHEX(SUBSTRING($Data,11,2)),UNHEX(SUBSTRING($Data,9,2)),
                    UNHEX(SUBSTRING($Data,15,2)),UNHEX(SUBSTRING($Data,13,2)),
                    UNHEX(SUBSTRING($Data,17,16)));
    END IF;
    RETURN $Result;
END

$$

CREATE FUNCTION `ToGuid`(
    $Data BINARY(16)
) RETURNS char(36) CHARSET utf8
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result CHAR(36) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Result =
            CONCAT(
                HEX(SUBSTRING($Data,4,1)), HEX(SUBSTRING($Data,3,1)),
                HEX(SUBSTRING($Data,2,1)), HEX(SUBSTRING($Data,1,1)), '-', 
                HEX(SUBSTRING($Data,6,1)), HEX(SUBSTRING($Data,5,1)), '-',
                HEX(SUBSTRING($Data,8,1)), HEX(SUBSTRING($Data,7,1)), '-',
                HEX(SUBSTRING($Data,9,2)), '-', HEX(SUBSTRING($Data,11,6)));
    END IF;
    RETURN $Result;
END
$$

CHAR(16) is actually a BINARY(16), choose your preferred flavour

To follow the code better, take the example given the digit-ordered GUID below. (Illegal characters are used for illustrative purposes - each place a unique character.) The functions will transform the byte ordering to achieve a bit order for superior index clustering. The reordered guid is shown below the example.

12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
78563412-BC9A-FGDE-HIJK-LMNOPQRSTUVW

Dashes removed:

123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW

Solution 4:

char(36) would be a good choice. Also MySQL's UUID() function can be used which returns a 36-character text format (hex with hyphens) which can be used for retrievals of such IDs from the db.