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 :
Your new connection string will be :