We advise the following best practices for using MySQL Server with .NET code.
max_allowed_packet server variable controls the maximum size of a SQL statement that can
be sent to the server, or the largest individual row that can be returned. Before MySQL 8.0, this
defaulted to 4MiB, which is too small for many applications.
In MySQL 8.0, the new default is 64MiB; if you’re running an earlier server version you should set
max_allowed_packet=64M (or higher).
MySqlConnector is a replacement ADO.NET connector library (with many contributions from Faithlife developers). It provides true async I/O, has better performance, and fixes many bugs in MySQL Connector/NET (aka MySql.Data).
All text columns should use the
utf8mb4 character set, as it allows the full range of Unicode to be
stored. (See In MySQL, never use “utf8”. Use “utf8mb4”. for more details.)
By default, we prefer to use a binary collation:
"a" != "A"
If you have human-readable textual data where you’re certain that you want the database to perform
non-ordinal equality comparison and ordering, then consider using
utf8mb4_0900_ai_ci (or decide
if another collation is more appropriate for your use case).
Note: The default character set has changed from
utf8mb4 in MySQL 8.0.
In MySQL Server,
BOOL is an alias for
The MySQL ADO.NET connector understands this convention and will marshal
to managed code as the C#
bool type (
BOOL alias when defining columns in your SQL statements. Do not use
BIT(1) (which gets
mapped as a
ulong) to represent a Boolean value.
As a corollary to the above, avoid explicitly using
TINYINT(1). If you need a one-byte integer,
TINYINT UNSIGNED). The
(1) suffix simply indicates the “display width”
(which is typically ignored by .NET programs), not the number of bytes used for storage.
To store a
CHAR(36) [NOT NULL] COLLATE ascii_general_ci.
UUID() function returns a GUID in this format. The MySQL ADO.NET connector understands this
convention and will marshal
CHAR(36) back to managed code as the .NET
The collation is
If you’re storing millions of GUIDs, you may wish to consider using
performing custom data transfer. This will save 20 bytes per value, which may significantly
reduce your data storage needs. Note that for a
will return a
MySqlDataReader.GetGuid will reinterpret the value as a
Uses Unicode when transferring queries to and results from the server, avoiding potential data loss from encoding errors.
Note: this option is
utf8mb4 by default in MySqlConnector
With connection pooling on, but
ConnectionReset=False, temporary tables and session variables from
the last connection to the database will be retained. This is almost never what you want, and can lead
Note: This option is True by default in MySqlConnector
Posted by Bradley Grainger on October 30, 2017