SNOMED Documentation Search


 Other Documents
Skip to end of metadata
Go to start of metadata

Current Version - Under Revision

The following table provides example mapping from the SNOMED CT RF2datatypes to appropriate datatypes supported by MySql.

Table 2. Example Datatype Mappings

 

 

  RF2Datatype

MySql Datatype

Comment on Mapping

SCTID

BIGINT

Both these datatypes represent 64-bit integers.

UUID

BINARY(16)

MySql does not have a native datatype for UUID. The BINARY(16) representation is most economical for storage and most efficient for indexing. This requires a transformation on storage or review. The example queries in this guide use the simple transformations functions shown in .

An alternative is to use CHAR or VARCHAR representations. This does not require the transformations noted above. However, use of VARCHAR (36) costs 38 bytes rather than 16 bytes per UUID and due to use of UTF8 using CHAR (36) consumes a fixed 108 bytes per UID in a MySql table. More importantly the index performance is poorer for these string representations.

Integer

INT

Both these datatypes represent 32-bit integers.

String

VARCHAR (Len)

VARCHAR is used in preference to CHAR as it provides more space efficient storage.

Note that in the UTF8 encoded tables required for the MyISAM database reserves three bytes per character for fixed length strings. In contrast VARCHAR uses the number of bytes actually plus one or two bytes to specify length. Use of VARCHAR does result in some loss of performance but strings are only used in Descriptions, string refsetsand Identifiertables. In all these cases strings with a significant range of lengths are used and the space penalty for using CHAR datatypes would be high.

Boolean

TINYINT

MySql treats the datatype name boolean as an alias for TINYINT. In the examples this mapping is made explicit.

Time

DATETIME

This is the full representation of date and time and is used to ensure compatibility with existing data and potential accommodation of time stamped data.

The more compact DATE type could be used with current data as the effectiveTime is currently a date only representation. However, the more flexible DATETIME has been preferred in the examples because this emphasizes the fact that in an International environment the effectiveTime implies the UTC time and thus the date alone is not a precise representation.

Table 3. Example UUID transformation

Action

 

Load or insert to storage

SET [column-name] = UNHEX(REPLACE(@uid,'-',''))

Select from storage

RenderUid([column-name])

UNHEX

A built in MySql function that converts a hexadecimal string to binary.

RenderUid

FUNCTION `RenderUid`(Uid blob) RETURNS varchar(36) CHARSET utf8

BEGIN

Set @Tmp = Hex(uid);

RETURN CONCAT(SUBSTRING(@Tmp,1,8),'-',SUBSTRING(@Tmp,9,4),'-',SUBSTRING(@Tmp,13,4),'-',SUBSTRING(@Tmp,17,4),'-',SUBSTRING(@Tmp,21));

END


Feedback