Why use BINARY type to store a GUID ?

GUID is a 128-bit (16 bytes) integer number. 

In its canonical textual representation, GUID are represented as 32 hexadecimal 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: 79d266c1-ca54-4bc9-9b0e-4a988fa1b1dc

It is possible to store it in its human-readable form and in this case we could consider using CHAR(36) but this storage form isn’t suitable for the following reasons :

• storing an integer number as hexadecimal string waste a lot of space
• the fours dashes are superfluous
• risk to store invalid (non hexa) data

Since Guid is a 16-byte number, storing it as 16 bytes would be more efficient.

BINARY(16) is therefore the most suitable MySQL data type. No character set, no collation, just 16 bytes.

Furthermore, if GUID type is used for primary key, the gain is even greater, as in InnoDB the primary key value is copied into all secondary index values.

How to configure NHibernate ?

The tests used to write this article were carried out with the following versions of libraries :

• NHibernate 5.3.4
• MySql.Data 8.0.22

For schema generation

If you plan to use the NHibernate SchemaExport tool to generate your schema from your mappings, you have to use MySQL5Dialect for your NHibernate configuration.

MySQL5Dialect is the only MySQL dialect that will generate BINARY(16) type fields. If you use others MySQL dialects, you will have to convert manually these fields to BINARY(16) after NHibernate generates the schema. (see table below)

For SQL Queries

In order to make the generated queries interpret GUID as BINARY(16), you should add old guids=true; to your connection string.

If your connection string is :

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Your new connection string will be :

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;old guids=true;