Re: utf-8 uuid varbinary
Posted by: Alaric Dailey
Date: February 20, 2010 08:58AM

There is no reason to make it a varying binary, the length of a uuid/guid is fixed 128 bits or 16 bytes thus binary(16) would be the proper choice.

The only reason you would need the 32 or 36 byte lengths is if you are storing the text representations (because each byte is represented as 2 characters) which exactly doubles the size. When you add the dashes in to make the value more human readable.

Obviously storing the text representation of a UUID introduces problems, greater required length, thus larger, slower indexes and possible problems with case-sensitivity.

So here is a test table

CREATE TABLE `testtable` (
`ID` binary(16) NOT NULL,
`Name` varchar(11) NOT NULL,
PRIMARY KEY (`Name`),
UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is a way to insert the value using raw dynamic sql in my testtable which is a binary(16) and varchar field for this uuid

{d1dd71a1-b114-4929-b63e-f8805e1b4fe4}


insert into `test`.`testtable`(ID, Name) values (0xD1DD71A1B1144929B63EF8805E1B4FE4, 'testrow')

and of course you can test it with

select * from `test`.`testtable` where ID=0xd1dD71A1B1144929B63EF8805E1B4FE4

and I made sure to test with a differnt case on the value to show that the casing doesn't matter if you store your value this way.

Options: ReplyQuote


Subject
Views
Written By
Posted
6591
January 02, 2010 04:25PM
4454
January 03, 2010 08:11PM
4004
February 20, 2010 08:39AM
Re: utf-8 uuid varbinary
3435
February 20, 2010 08:58AM
2290
February 24, 2010 07:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.