170

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

Improve this question
1
  • 1
    "thaBadDawg" offers a good answer. There is a parallel thread on Stack Overflow that discusses the topic. I added some comments to that threads answer that link to resources with more detail. Here is the question link: stackoverflow.com/questions/547118/storing-mysql-guid-uuids - I expect this topic to become more common when people start considering AWS and Aurora.
    – Zack Jannsen
    Commented Feb 4, 2016 at 10:58

10 Answers 10

Reset to default
121

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.

Improve this answer
12
  • 194
    Because with 16 bytes, you can generate things in different databases, on different machines, at different times, and still merge the data together seamlessly :)
    – Billy ONeal
    Commented Sep 2, 2010 at 0:12
  • 5
    need reply, what really is a char 16 binary? not char? not binary? I dont see that type in any of the mysql gui tools, nor any documentation in mysql site. @BillyONeal
    – nawfal
    Commented Jun 24, 2012 at 19:41
  • 3
    @nawfal: Char is the datatype. BINARY is the type specifier against the type. The only effect it has is to modify how MySQL does collation. See dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html for more details. Of course you can just use a BINARY type directly if your database editing tool allows you to do that. (Older tools don't know of the binary data type but do know of the binary column flag)
    – Billy ONeal
    Commented Jun 25, 2012 at 3:48
  • 2
    a CHAR and a BINARY field are essentially the same. If you want to take it to the most basic of levels, a CHAR is a binary field expecting a 0 to 255 value with the intention of representing said value with a value mapped from a lookup table (in most cases now, UTF8). A BINARY field expects the same kind of value without any intention of representing said data from a lookup table. I used CHAR(16) back in the 4.x days because back then MySQL wasn't as good as it is now.
    – thaBadDawg
    Commented Jun 27, 2012 at 16:10
  • 20
    There are several good reasons why a GUID is far better than a autoincrement. Jeff Atwood lists these one. To me, the best advantage in using a GUID is that my app won't need a database roundtrip to know the key of an entity: I could populate it programmatically, which I could not do if I were using an auto-increment field. This saved me from several headaches: with GUID I can manage the entity in the same way, regardless of the entity has been already persisted or it a brand new one.
    – Arialdo Martini
    Commented Dec 30, 2012 at 9:11
58

I would store it as a char(36).

Improve this answer
6
  • 6
    I can't see why you should store -s.
    – Afshin Mehrabani
    Commented Jan 30, 2017 at 15:30
  • 2
    @AfshinMehrabani It's simple, straightforward, human-readable. It's not necessary, of course, but if storing those extra bytes doesn't hurt then this is the best solution.
    – user1717828
    Commented Jan 4, 2018 at 15:21
  • 2
    Storing the dashes might not be a good idea because it will cause more overhead. If you want to make it human readable, make the application read with the dashes.
    – Lucca Ferri
    Commented Jul 23, 2018 at 2:45
  • @AfshinMehrabani another consideration is parsing it from the database. Most implementations will expect dashes in a valid guid.
    – Ryan Gates
    Commented Jun 19, 2019 at 21:58
  • 1
    @joedotnot It's a trade off and depends on how your data is going to be used. Formatting the GUID with dashes isn't free it will add CPU cycles to every query that requires it. Including them in the database will bloat the database itself and add extra CPU cycles to your queries. If your data is going to be write-many/read-few, then store it without the dashes and format as needed. If your data is going to have lots of queries run against it then you might be better off storing your data formatted with dashes.
    – Nick Fotopoulos
    Commented Apr 11, 2022 at 20:11
33

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
Improve this answer
7
  • Here's the above GuidToBinary without removing the hyphens from the string: CREATE FUNCTION GuidToBinary($guid char(36)) RETURNS binary(16) RETURN CONCAT( UNHEX(SUBSTRING($guid, 7, 2)), UNHEX(SUBSTRING($guid, 5, 2)), UNHEX(SUBSTRING($guid, 3, 2)), UNHEX(SUBSTRING($guid, 1, 2)), UNHEX(SUBSTRING($guid, 12, 2)), UNHEX(SUBSTRING($guid, 10, 2)), UNHEX(SUBSTRING($guid, 17, 2)), UNHEX(SUBSTRING($guid, 15, 2)), UNHEX(SUBSTRING($guid, 20, 4)), UNHEX(SUBSTRING($guid, 25, 12)));
    – Jonathan Oliver
    Commented Jan 15, 2013 at 14:33
  • 5
    For the curious, these functions are superior to just UNHEX(REPLACE(UUID(),'-','')) because it arranges the bits in an order that will perform better in a clustered index.
    – Slashterix
    Commented Feb 2, 2014 at 1:28
  • This is very helpful, but I feel it could be improved with a source for CHAR and BINARY equivalency (the docs seem to imply there are important differences and an explanation of why clustered index performance is better with reordered bytes.
    – Patrick M
    Commented Sep 8, 2014 at 21:48
  • When I use this my guid is changed. I've tried inserting it using both unhex(replace(string, '-', '')) and the function above and when I convert them back using the same methods the guid that is selected is not the one that was inserted. What is transforming the guid? All I've done is copied the code from above.
    – Misbit
    Commented Dec 17, 2015 at 14:53
  • 1
    Wow too verbose. i'll stick with UNHEX(REPLACE(UUID(),'-','') and nested insert functions insert( insert( insert( insert(HEX(MyBin16Col),9,0,'-'), 14,0,'-'), 19,0,'-'), 24,0,'-')
    – joedotnot
    Commented Jan 25, 2020 at 8:17
28

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.

Improve this answer
25

"Better" depends on what you're optimizing for.

How much do you care about storage size/performance vs. ease of development? More importantly - are you generating enough GUIDs, or fetching them frequently enough, that it matters?

If the answer is "no", char(36) is more than good enough, and it makes storing/fetching GUIDs dead-simple. Otherwise, binary(16) is reasonable, but you'll have to lean on MySQL and/or your programming language of choice to convert back and forth from the usual string representation.

Improve this answer
2
  • 3
    If you host the software (ie a web page for example) and don't sell/install in the client, you can always start with char(36) for easy development in the early stage of the software, and mutate to a more compact format as the system grows in usage and starts needing optimization.
    – Xavi Montero
    Commented Sep 13, 2014 at 8:27
  • 1
    The biggest down side of the much larger char(36) is how much space the index will take. If you have large number of records in the database, you are doubling the size of the index.
    – bpeikes
    Commented Jul 15, 2015 at 18:58
8

Binary(16) would be fine, better than use of varchar(32).

Improve this answer
7

The GuidToBinary routine posted by KCD should be tweaked to account for the bit layout of the timestamp in the GUID string. If the string represents a version 1 UUID, like those returned by the uuid() mysql routine, then the time components are embedded in letters 1-G, excluding the D.

12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
12345678 = least significant 4 bytes of the timestamp in big endian order
9ABC     = middle 2 timestamp bytes in big endian
D        = 1 to signify a version 1 UUID
EFG      = most significant 12 bits of the timestamp in big endian

When you convert to binary, the best order for indexing would be: EFG9ABC12345678D + the rest.

You don't want to swap 12345678 to 78563412 because big endian already yields the best binary index byte order. However, you do want the most significant bytes moved in front of the lower bytes. Hence, EFG go first, followed by the middle bits and lower bits. Generate a dozen or so UUIDs with uuid() over the course of a minute and you should see how this order yields the correct rank. 

select uuid(), 0
union 
select uuid(), sleep(.001)
union 
select uuid(), sleep(.010)
union 
select uuid(), sleep(.100)
union 
select uuid(), sleep(1)
union 
select uuid(), sleep(10)
union
select uuid(), 0;

/* output */
6eec5eb6-9755-11e4-b981-feb7b39d48d6
6eec5f10-9755-11e4-b981-feb7b39d48d6
6eec8ddc-9755-11e4-b981-feb7b39d48d6
6eee30d0-9755-11e4-b981-feb7b39d48d6
6efda038-9755-11e4-b981-feb7b39d48d6
6f9641bf-9755-11e4-b981-feb7b39d48d6
758c3e3e-9755-11e4-b981-feb7b39d48d6

The first two UUIDs were generated closest in time. They only vary in the last 3 nibbles of the first block. These are the least significant bits of the timestamp, which means we want to push them to the right when we convert this to an indexable byte array. As a counter example, the last ID is the most current, but the KCD's swapping algorithm would put it before the 3rd ID (3e before dc, last bytes from the first block).

The correct order for indexing would be:

1e497556eec5eb6... 
1e497556eec5f10... 
1e497556eec8ddc... 
1e497556eee30d0... 
1e497556efda038... 
1e497556f9641bf... 
1e49755758c3e3e...

See this article for supporting information: http://mysql.rjweb.org/doc.php/uuid

*** note that I don't split the version nibble from the high 12 bits of the timestamp. This is the D nibble from your example. I just throw it in front. So my binary sequence ends up being DEFG9ABC and so on. This implies that all my indexed UUIDs start with the same nibble. The article does the same thing.

Improve this answer
2
  • 1
    is the purpose of this to save storage space? or to make sorting them useful?
    – MD004
    Commented Feb 22, 2017 at 21:27
  • 1
    @MD004. It creates a better sort index. The space stays the same.
    – bigh_29
    Commented Mar 1, 2017 at 17:57
5

For those just stumbling across this, there is now a much better alternative as per research by Percona.

It consists of reorganising the UUID chunks for optimal indexing, then converting into binary for reduced storage.

Read the full article here

Improve this answer
3
  • I read that article before. I find it very interesting but then how should we perform a query if we want to filter by an ID which is binary? I guess we need to hex again and then apply the criteria. Is it so demanding? Why to store binary(16) (sure it is better than varchar(36)) instead of bigint of 8 bytes?
    – Maximus Decimus
    Commented Sep 26, 2016 at 21:03
  • 3
    There's an updated article from MariaDB which should answer your question mariadb.com/kb/en/mariadb/guiduuid-performance
    – SleepyCal
    Commented Sep 28, 2016 at 14:40
  • fwiw, UUIDv4 is completely random and needs no chunking.
    – Mahmoud Al-Qudsi
    Commented Jan 12, 2018 at 0:49
2

I would suggest using the functions below since the ones mentioned by @bigh_29 transforms my guids into new ones (for reasons I don't understand). Also, these are a little bit faster in the tests I did on my tables. https://gist.github.com/damienb/159151

DELIMITER |

CREATE FUNCTION uuid_from_bin(b BINARY(16))
RETURNS CHAR(36) 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
|

CREATE FUNCTION uuid_to_bin(s CHAR(36))
RETURNS BINARY(16) DETERMINISTIC
RETURN UNHEX(CONCAT(LEFT(s, 8), MID(s, 10, 4), MID(s, 15, 4), MID(s, 20, 4), RIGHT(s, 12)))
|

DELIMITER ;
Improve this answer
-4

if you have a char/varchar value formatted as the standard GUID, you can simply store it as BINARY(16) using the simple CAST(MyString AS BINARY16), without all those mind-boggling sequences of CONCAT + SUBSTR.

BINARY(16) fields are compared/sorted/indexed much faster than strings, and also take two times less space in the database

Improve this answer
2
  • 2
    Running this query shows that CAST converts the uuid string to ASCII bytes: set @a = uuid(); select @a, hex( cast(@a AS BINARY(16))); I get 16f20d98-9760-11e4-b981-feb7b39d48d6 : 3136663230643938 2D 39373630 2D 3131 (spaces added for formatting). 0x31=ascii 1, 0x36=ascii 6. We even get 0x2D, which is the hyphen. This isn't much different than just storing the guid as a string, except that you truncate the string at the 16th character, which cleaves off the part of the ID that is machine specific.
    – bigh_29
    Commented Jan 8, 2015 at 18:06
  • Yes, this is simply truncation. select CAST("hello world, this is as long as uiid" AS BINARY(16)); produces hello world, thi
    – MD004
    Commented Feb 22, 2017 at 21:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.