MySQL Connector/NET 6.9.8 and Boolean Values

There's a lot of confusion out there about how to store Boolean values in MySQL. Some people will recommend that you use the BIT type and some people will recommend that you use TINYINT(1).

First of all, the BIT type on MySQL is not the same BIT type on Microsoft SQL Server. The Microsoft SQL Server BIT type stores simple { False, True } values. Most of the other major relational database servers out there, such as PostgreSQL and Oracle, use a Boolean data type to store Boolean values. These work exactly like the Microsoft SQL Server BIT type.

MySQL's BIT data type is a different beast. It's used to store bit field values. Each column of this type can store 64 Booleans as it's stored in a 64-bit wide column. This means that if you're storing a single Boolean value in a BIT column on MySQL you'll be using up 8 bytes of storage while if you used a TINYINT(1) column you'd be using a single byte.

If you're using MySQL Connector/Net (I'm using version 6.9.6), BIT values are converted to ulong values in .NET. If you're only storing single Boolean values in all your BIT columns it's trivial to convert this value in c#:

bool value = dbvalue != 0;  

This forced manual type conversion along with wasting 7 bytes of storage per BIT column really points out that this isn't the correct data type to be using here.

The MySQL documentation says that there are BOOL and BOOLEAN types which are synonyms for TINYINT(1). There's no concrete type specifically for Boolean values in MySQL. Further, TRUE and FALSE are aliases for 1 and 0 respectively. Clearly the intent on the part of the MySQL developers was to use TINYINT(1) to store Boolean values.

MySQL Connector/NET by default maps TINYINT(1) to the .NET bool type. You can override this behavior by appending Treat Tiny As Boolean=False to your connection string which will map it to sbyte (or byte for UNSIGNED TINYINT(1) columns). Unfortunately, there appears to be a bug in MySQL Connector/NET 6.9.8 (which is the latest non-development release at the time of writing).

Let's say you create this table:

CREATE TABLE Foo  
(
  Id BIGINT NOT NULL,
  Bar TINYINT(1) NULL
);

And you insert a few values:

INSERT INTO Foo  
    (Id, Bar)
VALUES  
    (1, NULL),
    (2, TRUE),
    (3, FALSE),
    (4, NULL);

Which would look like this in MySQL Workbench:

Id   Bar  
==   ===
1    NULL  
2    1  
3    0  
4    NULL  

Switching over to c#, here's a bit of code that uses a MySQL Connector/NET MySqlConnection instance to query the database.

using (var query = new MySqlCommand(q, conn))  
{
    var reader = query.ExecuteReader();
    while (reader.Read())
    {
        var o = reader["Bar"];
        Console.WriteLine($"Bar: {o.ToString()} Type: {o.GetType().Name}");
    }
}

This first query will put the two non-null values first in the result set:

var q = @"SELECT * FROM Foo ORDER BY Bar DESC;";  

Output:

Bar: True Type: Boolean  
Bar: False Type: Boolean  
Bar:  Type: DBNull  
Bar:  Type: DBNull  

Grand. Works.

Now let's put the two null values first:

var q = @"SELECT * FROM Foo ORDER BY Bar;";  

Output:

Bar:  Type: DBNull  
Bar:  Type: DBNull  
Bar: 0 Type: SByte  
Bar: 1 Type: SByte  

Not so grand.

You can change your connection string to have Treat Tiny As Boolean=False and the first query will produce SByte. Unfortunately, if you use Treat Tiny As Boolean=True, the second query still produces SByte.

It seems that there's a bug in MySQL Connector/NET 6.9.8 when setup to Treat Tiny As Boolean=True (which is the default). If the first row in a result set contains a null value for a TINYINT(1) column, it will map the values to SByte/sbyte for all rows. On the other hand, if the first row in a result set contains 0 or 1 for a TINYINT(1) column, it correctly maps all the values to Boolean/bool.

This is not a problem for TINYINT(1) NOT NULL columns as the bug is triggered with a NULL value in the first row of a result set.