This project has moved. For the latest updates, please go here.

Using MySQL With GUID or UUID in .NET (C#)

On 18 Oct 2012, Joffnerd had reported an issue: Unhandled data type: System.Guid

Data type of System.Guid is not handled.

This bug is fixed in V1.5.2 beta.

Below are some of the information gathered in the process of solving this bug.

Contents

  1. Basic Introduction to GUID/UUID
  2. How does GUID/UUID Works Between MySQL and .NET?
  3. How to Insert System.GUID into MySql Database?
  4. How Is MySqlBackup.NET Handle Data Type of GUID/UUID?
  5. References and Resources


1. Basic Introduction to GUID (Global Unique Identifier) or UUID (Universal Unique Identifier)

The main function of GUID/UUID is same as what we have already familiar with - the Auto-Increment Integer, to serve as an Unique Identifier.

Examples retrieved from: Coding Horror: Primary Keys: IDs versus GUIDs

Traditional Auto-Increment Interger Primary Keys

ID  Value
--  -----
 1  Apple
 2  Orange
 3  Pear
 4  Mango

Example of GUID/UUID:

ID                                    Value
------------------------------------  -----
C87FC84A-EE47-47EE-842C-29E969AC5131  Apple
2A734AE4-E0EF-4D77-9F84-51A8365AC5A0  Orange
70E2E8DE-500E-4630-B3CB-166131D35C21  Pear
15ED815C-921C-4011-8667-7158982951EA  Mango

It appears in 2 forms, the visible form is comprised of 36 characters long, but the real value is stored as
byte[16]. In .NET programming language, GUID/UUID is stored in a type called System.Guid

What happen to Auto-Increment Integer?

The Auto-Increment Int is only Unique when it is working within it's own database, but it is no more Unique when multiple database is going to work together. For example, merging rows from different database. The rows cannot be merged directly as there are many of duplicates of the same ID. We need to do extra calculation for generating another unique string/number id to ensure they won't crash/duplicate. But what if there are lots of complex joins across tables within database itself? foreign keys? As a result, we'll need to loop through every rows of every tables, replacing or adding with new generated IDs. We'll face a very serious technical problem if we are working on large system which is worked across multiple databases. The Auto-Incremented ID will no longer unique.

But, this problem is solved by using GUID/UUID. The rows is not Auto-Incremented by numbers. It is something like hash value. The generation of the GUID/UUID might takes the machine id as part of the id. Therefore it is guaranteed that the generated ID will be truly unique throughout several databases. They can be worked and merged directly among multiple databases.

GUID/UUID Pros & Cons

 (retrieved from Coding Horror: Primary Keys: IDs versus GUIDs)

GUID Pros

  • Unique across every table, every database, every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database
  • Most replication scenarios require GUID columns anyway

GUID Cons

  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes


2. How does GUID/UUID Works Between MySQL and .NET?

There are 3 different of situations occur:

1st Situation:

If you are using Connector/NET older than 6.1.1, MySQL data type of BINARY(16) will be automatically converted into System.Guid in .NET.

2nd Situation:

Start from Connnector/NET 6.1.1, CHAR(36) will be automatically converted as System.GUID in .NET and BINARY(16) will be treated as System.Byte[].

3rd Situation:

If you use newer Connector/NET (newer than 6.1.1), and you want to use BINARY(16) as System.GUID (by default it is not), you have to add a connection option Old Guids=true in the Connection String.

Example:

server=localhost;user=root;password=qwerty;database=test;old guids=true;

in this case, CHAR(36) will be converted as System.String and BINARY(16) = System.GUID 

Read more about connection options of Old Guids at:

MySQL 5.6 Reference Manual: 21.2.6. Connector/Net Connection String Options Reference


3. How to Insert System.GUID into MySql Database

This is a sample table for storing a GUID/UUID.

CREATE TABLE `testdata` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `binary` binary(16) DEFAULT NULL,
  `char` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Insert System.GUID into MySQL:

string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
using (MySqlConnection conn = new MySqlConnection(constr))
{
    conn.Open();

    // Create a System.GUID
    byte[] ba = new byte[16];
    Random rd = new Random();
    rd.NextBytes(ba);
    System.Guid guid = new Guid(ba);

    // Prepare GUID values in SQL format
    string guidForChar36 = guid.ToString();
    string hexstring = BitConverter.ToString(guid.ToByteArray());
    string guidForBinary16 = "0x" + hexstring.Replace("-", string.Empty);

    string sql = "insert into testdata(`binary`,`char`)"
               + "values(" + guidForBinary16 + ","
               + "'" + guidForChar36 + "');";

    MySqlCommand cmd = new MySqlCommand(sql, conn);
    cmd.ExecuteNonQuery();

    conn.Close();
}

If you are using CHAR(36) to stored GUID/UUID, you can use this function to generate GUID/UUID:

UUID()

Example:

INSERT INTO mytable(id, table2) VALUES(UUID(), 'some data');


4. How Does MySqlBackup.NET Handle Data Type of GUID/UUID?

There are 2 possibilities that Connector/NET will return a value as System.Guid from MySQL to .NET Framework.

As mentioned above, the value might came from BINARY(16) or CHAR(36). We have no idea that developers will choose which solution to store their GUID/UUID. Therefore a table structure checking must be carried out before exporting GUID/UUID.

This SQL statement will gather information about the table.

SHOW FULL COLUMNS FROM `tablename`;

Sample Result:

Field Type Collation Null Key Default Extra Privileges Comment
id int(10) unsigned   NO PRI   auto_increment select,insert,update,references  
binary binary(16)   YES       select,insert,update,references  
char char(36) utf8_general_ci YES       select,insert,update,references  

From the table above, the source of data type can be identified at 2nd column - Type.

If it is came from BINARY(16), the value will be converted it into hexadecimal string.

string BuildGuidStrForBinary16()
{
    DataTable dt = new DataTable();
    using (MySqlConnection conn = new MySqlConnection("server=localhost;user=root;pwd=qwerty;database=test;"))
    {
        conn.Open();
        string sql = "SELECT `binary` FROM `tablename`;";
        MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
        da.Fill(dt);
        conn.Close();
    }
    System.Guid guid = (System.Guid)dt.Rows[0][0];
    byte[] ba = guid.ToByteArray();
    string guidStr = BitConverter.ToString(ba);
    guidStr = "0x" + guidStr.Replace("-", string.Empty);
    return guidStr;
}

Result:

0x594394EE15178ECC23FBB4456AF0B8D1

The INSERT statement:

INSERT INTO `tablename`(`binary`) VALUES(0x594394EE15178ECC23FBB4456AF0B8D1);

If it is came from CHAR(36), the value will be converted it into GUID/UUID string.

string BuildGuidStrForChar36()
{
    DataTable dt = new DataTable();
    using (MySqlConnection conn = new MySqlConnection("server=localhost;user=root;pwd=qwerty;database=test;"))
    {
        conn.Open();
        string sql = "SELECT `char` FROM `tablename`;";
        MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
        da.Fill(dt);
        conn.Close();
    }
    System.Guid guid = (System.Guid)dt.Rows[0][0];
    return guid.ToString();
}

Result:

ee944359-1715-cc8e-23fb-b4456af0b8d1

The INSERT statement:

INSERT INTO `tablename`(`char`) VALUES('ee944359-1715-cc8e-23fb-b4456af0b8d1');


5. References/Resources:

  1. Primary Keys: IDs versus GUIDs - http://www.codinghorror.com
    - A brief intro about GUID/UUID
  2. The Quick Guide to GUIDs - http://betterexplained.com
    - Analysis of the Usage of GUID/UUID
  3. Guid Structure - http://msdn.microsoft.com
    - The Class Structure of System.Guid in .NET Framework
  4. MySQL InnoDB Primary Key Choice: GUID/UUID vs Integer Insert Performance - http://kccoder.com
    - Benchmark, Statistics, Tables and Illustration of Performance of GUID/UUID and Auto-Increment Int 
  5. GUIDs as fast primary keys under multiple databases - http://www.codeproject.com
    This article outlines an approach for using GUID values as primary keys/clustered indexes that avoids most of the normal disadvantages, adapting the COMB model for sequential GUIDs.

Last edited Nov 3, 2012 at 6:33 AM by adriancs, version 17