UUID max character length

Question

We are using UUID as primary key for out oracle DB, and trying to determine an appropriate max character length for the VARCHAR. Apparently this is 36 characters but we have noticed UUID'S generated which are longer than this - up to 60 characters in length. Does anyone know a suitable max char length for UUID??

As a UUID is a 128 bits number I'm really curious to see what coding would convert it into a 60 char string. Looks like either extremely poor encoding, or some other, unrealted problem to me. — fvu, Nov 15 '12 at 11:57
What's your RDBMS? MS SQL has a dedicated type for UUIDs, and others can simply store the bytes. Is there some reason you'd want to store these as `VARCHAR`s? — , Aug 01 '14 at 22:15
@user565869 storing them as bytes are terrible for any kind of manual inspection — Enerccio, Apr 26 '18 at 06:01

score 227 · Answer 1 · edited Oct 07 '21 at 07:34

227

Section 3 of RFC4122 provides the formal definition of UUID string representations. It's 36 characters (32 hex digits + 4 dashes).

Sounds like you need to figure out where the invalid 60-char IDs are coming from and decide 1) if you want to accept them, and 2) what the max length of those IDs might be based on whatever API is used to generate them.

edited Oct 07 '21 at 07:34

Community

1
1

answered Nov 15 '12 at 20:07

broofa

37,461
11
73
73

score 86 · Answer 2 · edited Jul 15 '19 at 13:23

86

This is the perfect kind of field to define as CHAR 36, by the way, not VARCHAR 36, since each value will have the exact same length. And you'll use less storage space, since you don't need to store the data length for each value, just the value.

edited Jul 15 '19 at 13:23

RiggsFolly

93,638
21
103
149

answered Sep 24 '13 at 18:13

apotek

1,081
8
8

10

CHAR may use more space than VARCHAR if your character set on the column is multi-byte (see bottom part on http://stackoverflow.com/a/59686/1691446) – David Jun 22 '16 at 09:51
8

Pretty sure UUIDv4 is only using the latin-1 charset of UTF-8, in which case this wont be affected. Definitely check if you are using a different charset though. – Aaron_H May 12 '17 at 16:24
2

UUID in string format can only use this set of characters (regex): `[0-9A-Fa-f-]`, which is 23 distinct octets in ASCII. – cowbert Mar 05 '18 at 23:11
2

RFC 4122 says UUIDs are 16 octets or 128 bits. If you're using more than that much storage, you're inefficiently encoding them. No need to encode the dashes, for example. They add no information. – Trenton Mar 30 '18 at 20:52
10

@Trenton there's a trade-off between storage efficiency and user friendliness. One could store UUIDs as BINARY(16) for maximum storage efficiency, but someone looking over the DB would not see the canonical representation, and a programming language may only have a means of creating a UUID object from the canonical/string representation, or not have a UUID object type at all; the UUID might be stored in string form in a file, making comparison with the binary form cumbersome, etc. – TaylanKammer Jun 12 '18 at 07:50

score 22 · Answer 3 · answered Jul 31 '18 at 20:43

22

Most databases have a native UUID type these days to make working with them easier. If yours doesn't, they're just 128-bit numbers, so you can use BINARY(16), and if you need the text format frequently, e.g. for troubleshooting, then add a calculated column to generate it automatically from the binary column. There is no good reason to store the (much larger) text form.

answered Jul 31 '18 at 20:43

StephenS

1,813
13
19

It's ridiculus that you have the least upvotes. This should be the correct answer to the problem. – COMMANDER CAPSLOCK Nov 03 '22 at 14:41
2

@COMMANDERCAPSLOCK To be fair to other voters, I answered 6 years late. – StephenS Nov 19 '22 at 20:30

score 0 · Answer 4 · answered Jun 30 '23 at 20:46

I know the question is many years old but using Oracle now they do have a UUID data type you could use. From their site:

A universally unique identifier (UUID) is a 128-bit number used to identify information in computer systems. You can create a UUID and use it to uniquely identify something. In its canonical textual representation, the 16 octets of a UUID are represented as 32 hexadecimal (base-16) digits, displayed in five groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 hexadecimal characters and 4 hyphens). For example, a81bc81b-dead-4e5d-abff-90865d1e13b1.

So as mentioned above you can use either the UUID type or use a character string of length 36.

You can see more information about the UUID data type from Oracle here: https://docs.oracle.com/en/database/other-databases/nosql-database/21.1/sqlreferencefornosql/using-uuid-data-type.html#GUID-D2BEE2A1-BF6D-4F4A-8EF4-059A6C8FBAA6

Here's a list of other databases with UUID supported.

MySQL https://blogs.oracle.com/mysql/post/mysql-uuids https://dev.mysql.com/blog-archive/storing-uuid-values-in-mysql-tables/

Microsoft SQL https://learn.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-ver16

Postgres https://www.postgresql.org/docs/current/datatype-uuid.html

MongoDB https://www.mongodb.com/docs/manual/reference/method/UUID/