MySQL中的enum或char(1) [英] enum or char(1) in MySQL

问题描述

有时我不确定在MysQL中使用enum还是char(1).例如，我存储帖子的状态.通常，我只需要status字段中的 Active 或 Passive 值.我有两个选择:

Sometimes I am not sure whether using enum or char(1) in MysQL. For instance, I store statuses of posts. Normally, I only need Active or Passive values in status field. I have two options:

// CHAR
status char(1);

// ENUM (but too limited)
status enum('A', 'P');

如果以后我想再添加一种状态类型(即Hidden)怎么办?如果我的数据很少，那将不是问题.但是，如果我的数据太大，我想编辑ENUM类型将是个问题.

What about if I want to add one more status type (ie. Hidden) in the future? If I have small data, it won't be an issue. But if i have too large data, so editing ENUM type will be problem, i think.

那么，如果我们还考虑MySQL性能，您有何建议?我会走哪条路?

So what's your advice if we also think about MySQL performance? Which way I would go?

推荐答案

都没有.您通常会在查询表中使用tinyint

Neither. You'd typically use tinyint with a lookup table

• char(1)会稍慢一些，因为比较使用排序规则

• char(1) will be slightly slower because comparing uses collation

混乱:当您扩展到超过A和P

confusion: As you extend to more than A and P

使用字母限制您添加更多类型.请参阅最后一点.

using a letter limits you as you add more types. See last point.

我所见过的每个系统都有一个以上的客户端，例如报告.对于每个客户端代码，A和P必须解析为主动"和被动"

every system I've seen has more then one client eg reporting. A and P have to resolved to Active and Passive for in each client code

可扩展性:再添加一种类型(对于"Suspended"为"S")，您可以将一行添加到查找表或更改许多代码和约束.还有您的客户代码

extendibility: add one more type ("S" for "Suspended") you can one row to a lookup table or change a lot of code and constraints. And your client code too

维护:逻辑位于3个位置:数据库约束，数据库代码和客户端代码.通过查找和外键，它可以放在一个地方

maintenance: logic is in 3 places: database constraint, database code and client code. With a lookup and foreign key, it can be in one place

枚举不可移植

在使用单个字母或枚举的正方面

On the plus side of using a single letter or Enum

注意:有一个相关的DBA.SE 有关Enums的MySQL问题.建议也在那里使用查找表.

Note: there is a related DBA.SE MySQL question about Enums. The recommendation is to use a lookup table there too.

这篇关于MySQL中的enum或char(1)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！