What is the Proper Data Type for Storing UUIDs in MySQL?

While creating a schema for SNOMED to ICD-10 mapping, I came across the requirement of storing a UUID (128-bit unsigned integer) as the ‘id’ field. 128-bits = 16 bytes so we’ll use BINARY(16).

drop table if exists `snomedct_to_icd10_refset_descriptor_US_20140901`;
create table `snomedct_to_icd10_refset_descriptor_US_20140901` (
  id binary(16) not null primary key, 
  effectiveTime DATE not null,
  active tinyint(1) unsigned not null default 0,
  moduleId bigint unsigned not null default 0,
  refSetId bigint unsigned not null default 0,
  referencedComponentId bigint unsigned not null default 0,
  attributeDescription bigint not null default 0,
  attributeType bigint unsigned not null default 0,
  attributeOrder int unsigned null
);

In order to store the UUID properly, we need to remove the dashes and convert the hexadecimal representation to its binary form. This could be done in a single step with: UNHEX(REPLACE(‘id’, ‘-‘, ”)). In my case, I’m loading it from the a file so it’s done this way:

load data infile '/tmp/der2_cciRefset_RefsetDescriptorSnapshot_US1000124_20140901.txt' 
into table `snomedct_to_icd10_refset_descriptor_US_20140901`
fields terminated by '\t' 
lines terminated by '\n'
ignore 1 lines
(@var1, effectiveTime, active, moduleId, refSetId, referencedComponentId, attributeDescription, attributeType, attributeOrder)
set id = unhex(replace(@var1, '-', ''));

Of course when retrieving the ‘id’ field, you’ll need to HEX(‘id’).