关于MySql/Mariadb上UUID在C#上的处理

在C#中处理MySql/Mariadb中的UUID最简单的方法是把Guid直接转换成CHAR(36)存到MySql/Mariadb数据库中。但是，这样的话有一定局限性，那就是当数据表对大小写敏感时，查询时可能因为大小写的问题找不到正确的UUID值。在Mariadb的官方文档上推荐的是将UUID存储为Binary(16)。并给出了两个用来处理UUID的函数：

DELIMITER //

CREATE FUNCTION UuidToBin(_uuid BINARY(36))
    RETURNS BINARY(16)
    LANGUAGE SQL DETERMINISTIC
    CONTAINS SQL SQL SECURITY INVOKER
    RETURN
        UNHEX(CONCAT(
                SUBSTR(_uuid, 15, 4),
                SUBSTR(_uuid, 10, 4),
                SUBSTR(_uuid, 1, 8),
                SUBSTR(_uuid, 20, 4),
                SUBSTR(_uuid, 25)));
//
CREATE FUNCTION UuidFromBin(_bin BINARY(16))
    RETURNS BINARY(36)
    LANGUAGE SQL DETERMINISTIC
    CONTAINS SQL SQL SECURITY INVOKER
    RETURN
        LCASE(CONCAT_WS('-',
                        HEX(SUBSTR(_bin, 5, 4)),
                        HEX(SUBSTR(_bin, 3, 2)),
                        HEX(SUBSTR(_bin, 1, 2)),
                        HEX(SUBSTR(_bin, 9, 2)),
                        HEX(SUBSTR(_bin, 11))
            ));

//
DELIMITER ;

为了对应这样的操作，我用C#写了一个MySqlUUID的数据结构对应上面两个函数：

    struct MySqlUUID
    {
        private readonly byte[] buffer;

        public MySqlUUID(byte[] buffer)
        {
            this.buffer = buffer;           
        }

        public MySqlUUID(Guid guid)
        {
            byte[] guidBuffer = guid.ToByteArray();
            this.buffer = ConvertGuidByteArrayToByteArray(guidBuffer);
        }

        public static byte[] ConvertToGuidByteArray(byte[] buffer)
        {
            byte[] result = new byte[16];

            for (int i = 0; i < 16; i++)
            {
                if (i < 8)
                {
                    result[7 - i] = buffer[i];
                }
                else
                {
                    result[i] = buffer[i];
                }
            }

            return result;
        }

        public static MySqlUUID NewUUID()
        {
            Guid guid = Guid.NewGuid();
            MySqlUUID mySqlUUID = new MySqlUUID(guid);

            return mySqlUUID;
        }

        public static implicit operator Guid(MySqlUUID mySqlUUID)
        {
            byte[] result = ConvertToGuidByteArray(mySqlUUID.buffer);
            Guid guid = new Guid(result);
            return guid;
        }

        public static implicit operator MySqlUUID(Guid guid)
        {
            byte[] result = ConvertGuidByteArrayToByteArray(guid.ToByteArray());
            MySqlUUID mySqlUUID = new MySqlUUID(result);
            return mySqlUUID;
        }

        public static implicit operator byte[](MySqlUUID mySqlUUID)
        {
            return mySqlUUID.buffer;
        }

        public static implicit operator MySqlUUID(byte[] buffer)
        {
            MySqlUUID mySqlUUID = new MySqlUUID(buffer);
            return mySqlUUID;
        }

        public byte[] ToByteArray()
        {
            return this.buffer;
        }

        public override string ToString()
        {
            byte[] result = ConvertToGuidByteArray(this.buffer);
            Guid guid = new Guid(result);
            return guid.ToString();
        }

        private static byte[] ConvertGuidByteArrayToByteArray(byte[] buffer)
        {
            byte[] result = new byte[16];

            for (int i = 0; i < 16; i++)
            {
                if (i < 8)
                {
                    result[i] = buffer[7 - i];
                }
                else
                {
                    result[i] = buffer[i];
                }
            }

            return result;
        }
    }

下面是验证代码：

        static void Main(string[] args)
        {
            MySqlConnectionStringBuilder mcsb = new MySqlConnectionStringBuilder()
            {
                Database = "test",
                Server = "192.168.101.124",
                Port = 3306,
                UserID = "root",
                Password = "Admin123456",
            };

            using (MySqlConnection con = new MySqlConnection(mcsb.ConnectionString))
            {
                con.Open();
                var tran = con.BeginTransaction();
                MySqlUUID guid = Guid.NewGuid();

                try
                {
                    using (MySqlCommand command = con.CreateCommand())
                    {
                        command.CommandText = "INSERT INTO `test1`(`Value`, `Origin`) VALUES (@value, @origin)";
                        command.Transaction = tran;
                        command.CommandType = CommandType.Text;

                        command.Parameters.AddWithValue("@value", guid.ToByteArray());
                        command.Parameters.AddWithValue("@origin", guid.ToString());
                        command.ExecuteNonQuery();
                        tran.Commit();
                    }
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                     throw ex;
                }

                using (MySqlCommand command = con.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM `test1` WHERE `Value` = @value";
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddWithValue("@value", guid.ToByteArray());

                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MySqlUUID uuid = (byte[])reader[0];
                            string origin = Convert.ToString(reader[1]);

                            Console.WriteLine($"{uuid}, {origin.ToUpper()}");
                        }
                    }
                }
            }
        }
    }