MySQL连接器在枚举后附加值比实际值小一 [英] MySQL Connector appending the enum with the value one less than the actual one

问题描述

我正在使用 MySQL连接器 for .NET来管理MySQL数据库来自C#.

I'm using the MySQL Connector for .NET to manage a MySQL database from C#.

当我尝试向数据库中插入一个枚举时，它会在枚举后面附加一个比实际值小的值.

When I try to insert an enum into the database it appends the enum with the value one less than the actual one.

public enum MyEnum {
    FirstValue, SecondValue, ThirdValue;
}

public void InsertEnum() {
    MySqlConnection con = new MySqlConnection(connStr);
    string sql = "INSERT INTO table (Col1) VALUES (@enumVal);";
    MySqlCommand cmd = new MySqlCommand(sql, con);
    cmd.Parameters.AddWithValue("@enumVal", MyEnum.SecondValue);
        //Note I'm trying to insert 'SecondValue' --^

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

这会将FirstValue插入数据库中.

数据库中的Col1列具有ENUM数据类型:

The Col1 column, in the database, is of the ENUM datatype:

ENUM('FirstValue','SecondValue','ThirdValue')

如果我尝试追加MyEnum.FirstValue，则会收到数据截断错误(data truncated for column COLUMN_NAME)，因为它尝试使用与.NET相同的索引(对于MyEnum.FirstValue，则为0)，这是保留的 MySQL枚举中的值(因为它们开始于1默认).

If I try to append MyEnum.FirstValue I get a data truncation error (data truncated for column COLUMN_NAME) since it tries to use the same index as .NET (0 in the case of MyEnum.FirstValue), which is a reserved value in MySQL Enums (since they start on 1 by default).

一种解决方案是将+1附加到所有枚举值或可能进行扩展:

A solution is to append +1 to all enum values or possibly make an extension:

cmd.Parameters.AddWithValue("@enumVal", MyEnum.SecondValue + 1);

对于我来说仍然感到错误，我必须手动将值修改为应该自动转换的库.

It still feels wrong to me that I have to modify the value manually, to a library that is supposed to convert it automatically.

我做错什么了吗，还是没有默认的方式来处理这个问题?

Am I doing something wrong, or is there no default way to deal with this?

推荐答案

.NET枚举默认情况下从零开始，并以整数为后缀(即，您可以将默认枚举转换为整数并返回).

.NET enumerations are by default zero-based, and backed with an integer (i.e. you can cast a default enumeration to an integer and back).

我想这是MySQL数据库驱动程序将枚举值转换为整数，然后尝试插入该整数的原因.

I imagine what is happening is that the MySQL database driver is casting the enum value to an integer, and trying to insert that.

MySQL将插入枚举列中的整数视为基于1的索引.

MySQL treats integers being inserted into enumeration columns as a 1-based index.

我建议仅使数据库列为整数而不是MySQL枚举.这样，零是有效值.

I would suggest just making the database column an integer instead of a MySQL enumeration. This way, zero is a valid value.

或者，您可以将枚举的第一个值声明为整数值1，以匹配MySQL，如下所示:

Alternatively, you could declare the first value of your enumeration to have an integer value of one, to match MySQL, like this:

public enum MyEnum 
{
    FirstValue = 1, 
    SecondValue, 
    ThirdValue
}

我想我正确地说，然后SecondValue和ThirdValue将分别得到值2和3.

I think I'm right in saying that SecondValue and ThirdValue would then be backed with values 2 and 3 respectively.

然后，当连接器将枚举转换为整数时，它将返回1、2和3，这将与MySQL的期望相匹配.

Then when the connector casts the enumeration to an integer, it will return 1, 2, and 3, which would match what MySQL expects.

第三种选择是在两端保持原样，然后使此映射成为数据层的功能(您确实有一个，对吧?)-然后，您只需要在一个地方处理即可. .请注意，这可能会对未来的开发人员造成误导.

The third alternative is to keep the types as-is at both ends, and just make this mapping a function of your data layer (you do have one, right?) - then you only need to deal with this in one place. Could be a little misleading for future developers, mind.

这篇关于MySQL连接器在枚举后附加值比实际值小一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！