At the beginning of the article, I will give you an interview question:
When designing a database for a small project (assuming MySQL is used), if you add a field (Roles) to the User table (Users) to store the user's roles, what type would you set for this field? Tip: Consider that roles need to be expressed in enumeration during back-end development, and a user may have multiple roles.
映入你脑海的第一个答案可能是：varchar 类型，用分隔符的方式来存储多个角色，比如用 1|2|3 或 1,2,3 来表示用户拥有多个角色。当然如果角色数量可能超过个位数，考虑到数据库的查询方便（比如用 INSTR 或 POSITION 来判断用户是否包含某个角色），角色的值至少要从数字 10 开始。方案是可行的，可是不是太简单了，有没有更好的方案？
A better answer should be integer (int, bigint, etc.). The advantage is that it is more convenient to write SQL query conditions and is superior to varchar in terms of performance and space. But integer is just one number after all. How can we express multiple characters? Now that you think of binary bit operations, you should already have the answer in mind.
Keep the answers in your heart, and then read this article. You may have unexpected gains, because you may encounter a series of problems in practical applications. In order to better explain the following issues, let's first review the basics of enumeration.
enum underlying
The purpose of an enumeration type is to restrict its variables to a limited number of options. These options (members of the enumeration type) each correspond to a number. The number starts at 0 by default and increments. For example:
public enum Days
{
Sunday, Monday, Tuesday, // ...
}
Where the value of Sunday is 0, Monday is 1, and so on. In order to see the value represented by each member at a glance, it is generally recommended to write out the member values explicitly and not omit them:
public enum Days
{
Sunday = 0, Monday = 1, Tuesday = 2, // ...
}
The type of C#enumeration member is the default type int. Through inheritance, you can declare the enumeration member to other types, such as:
public enum Days : byte
{
Monday = 1,
Tuesday = 2,
Wednesday = 3,
Thursday = 4,
Friday = 5,
Saturday = 6,
Sunday = 7
}
The enumeration type must be one that inherits from byte, sbyte, short, rush, int, uint, long, and ulong, and cannot be other types.
Here are some common uses of several enumerations (take the Days enumeration above as an example):
// 枚举转字符串
string foo = Days.Saturday.ToString(); // "Saturday"
string foo = Enum.GetName(typeof(Days), 6); // "Saturday"
// 字符串转枚举
Enum.TryParse("Tuesday", out Days bar); // true, bar = Days.Tuesday
(Days)Enum.Parse(typeof(Days), "Tuesday"); // Days.Tuesday
// 枚举转数字
byte foo = (byte)Days.Monday; // 1
// 数字转枚举
Days foo = (Days)2; // Days.Tuesday
// 获取枚举所属的数字类型
Type foo = Enum.GetUnderlyingType(typeof(Days))); // System.Byte
// 获取所有的枚举成员
Array foo = Enum.GetValues(typeof(MyEnum);
// 获取所有枚举成员的字段名
string[] foo = Enum.GetNames(typeof(Days));
Also, it's worth noting that enumerations can get unexpected values (values have no corresponding members). For example:
Days d = (Days)21; // 不会报错
Enum.IsDefined(typeof(Days), d); // false
Even if the enumeration does not have a member with a value of 0, its default value is always 0.
var z = default(Days); // 0
Enumeration can add useful auxiliary information to members through features such as Description and Display, such as:
public enum ApiStatus
{
[Description("成功")]
OK = 0,
[Description("资源未找到")]
NotFound = 2,
[Description("拒绝访问")]
AccessDenied = 3
}
static class EnumExtensions
{
public static string GetDescription(this Enum val)
{
var field = val.GetType().GetField(val.ToString());
var customAttribute = Attribute.GetCustomAttribute(field, typeof(DescriptionAttribute));
if (customAttribute == null) { return val.ToString(); }
else { return ((DescriptionAttribute)customAttribute).Description; }
}
}
static void Main(string[] args)
{
Console.WriteLine(ApiStatus.Ok.GetDescription()); // "成功"
}
I think these include most of the enumeration knowledge we use every day. Let's continue to return to the user role storage issue mentioned at the beginning of the article.
User role storage issues
Let's first define an enumeration type to represent two user roles:
public enum Roles
{
Admin = 1,
Member = 2
}
In this way, if a user has both Admin and Member roles, then the Roles field of the User table should have 3. Then the question arises. How should I write the SQL of all users with the Admin role at this time?
For basic programmers, this problem is very simple, just use the bit operator logical AND ('&') to query.
SELECT * FROM `User` WHERE `Roles` & 1 = 1;
In the same way, when querying users who have both roles, the SQL statement should read like this:
SELECT * FROM `User` WHERE `Roles` & 3 = 3;
Using C#to implement a query for this SQL statement is as follows (for simplicity, Dapper is used here):
public class User
{
public int Id { get; set; }
public Roles Roles { get; set; }
}
connection.Query<User>(
"SELECT * FROM `User` WHERE `Roles` & @roles = @roles;",
new { roles = Roles.Admin | Roles.Member });
Correspondingly, to determine whether a user has a certain role in C#, you can determine as follows:
// 方式一
if (user.Roles & Roles.Admin == Roles.Admin)
{
// 做管理员可以做的事情
}
// 方式二
if (user.Roles.HasFlag(Roles.Admin))
{
// 做管理员可以做的事情
}
Similarly, in C#you can perform arbitrary bit logical operations on enumerations, such as removing roles from an enumeration variable:
var foo = Roles.Admin | Roles.Member;
var bar = foo & ~foo;
This solves the problem of using integers to store multiple roles mentioned earlier in the article. Whether it is database or C#language, it is feasible in operation and very convenient and flexible.
Flags attribute of enumeration
Below we provide a method to query users by role and demonstrate how to call it, as follows:
public IEnumerable<User> GetUsersInRoles(Roles roles)
{
_logger.LogDebug(roles.ToString());
_connection.Query<User>(
"SELECT * FROM `User` WHERE `Roles` & @roles = @roles;",
new { roles });
}
// 调用
_repository.GetUsersInRoles(Roles.Admin | Roles.Member);
Roles.Admin | Roles.Member 的值是 3，由于 Roles 枚举类型中并没有定义一个值为 3 的字段，所以在方法内 roles 参数显示的是 3。3 这个信息对于我们调试或打印日志很不友好。在方法内，我们并不知道这个 3 代表的是什么。为了解决这个问题，C# 枚举有个很有用的特性：FlagsAtrribute。
[Flags]
public enum Roles
{
Admin = 1,
Member = 2
}
加上这个 Flags 特性后，我们再来调试 GetUsersInRoles(Roles roles) 方法时，roles 参数的值就会显示为 Admin|Member 了。简单来说，加不加 Flags 的区别是：
var roles = Roles.Admin | Roles.Member;
Console.WriteLing(roles.ToString()); // "3"，没有 Flags 特性
Console.WriteLing(roles.ToString()); // "Admin, Member"，有 Flags 特性
Adding the Flags feature to enumerations should be regarded as a best practice in C#programming. Try to add the Flags feature when defining enumerations.
Resolving enumeration value conflicts: Powers of 2
At this point, enumerating the type Roles seems fine, but what will happen if you want to add a role now: Mananger? According to the rule of increasing numerical values, the value of Manager should be set to 3.
[Flags]
public enum Roles
{
Admin = 1,
Member = 2,
Manager = 3
}
Can I set the value of Manager to 3? Obviously not, because Admin and Member perform bitwise OR logical operations (i.e. Admin| The value of Member) is also 3, which means that you have both roles, which conflicts with Manager. So how to set values to avoid conflicts?
Since the binary logical operation "OR" will conflict with the member value, use the rule of the logical operation or to resolve it. We know that the logic of the "OR" operation is that as long as a 1 appears on both sides, the result will be 1, for example 1.| 1、1| 0 results in 1, only 0| The result is 0 when the case is 0. Then we have to avoid any two values appearing 1 in the same position. According to the characteristics of binary that full 2 enters 1, it is enough to ensure that all values enumerated are powers of 2. For example:
1: 00000001
2: 00000010
4: 00000100
8: 00001000
If you add it later, it will be 16, 32, 64..., No matter how the values are added, they will not conflict with any value of the member. This solves the problem, so we define the values of the Roles enumeration like this:
[Flags]
public enum Roles
{
Admin = 1,
Member = 2,
Manager = 4,
Operator = 8
}
However, when defining values, you should do a little calculation in your mind. If you want to be lazy, you can use the following "displacement" method to define them:
[Flags]
public enum Roles
{
Admin = 1 << 0,
Member = 1 << 1,
Manager = 1 << 2,
Operator = 1 << 3
}
Just increment the code value continuously. The reading experience is good and it is not easy to make mistakes. The two methods are equivalent. The calculation of constant displacements is performed at compile time, so there is no extra overhead compared to them.
summary
This article uses a small interview question to trigger a series of thoughts on enumeration. In small systems, it is common to store user roles directly in the user table. At this time, setting the role field to an integer (such as int) is a better design solution. But at the same time, consider some best practices, such as using Flags features to help with better debugging and logging output. You should also consider various potential problems in actual development, such as performing or ('|') The problem where the operation conflicts with the member value.
