mysql enum char,MySQL中的enum或char（1）-CSDN博客

在MySQL中，对于存储有限状态的数据，如文章状态（活跃、停用），通常会考虑使用ENUM或CHAR(1)。ENUM虽然节省空间，但扩展性差，如果未来需要添加新的状态（如隐藏），则需要修改ENUM类型，这在大量数据时可能成为问题。CHAR(1)虽然稍慢，但避免了ENUM的扩展限制。建议使用TINYINT配合 lookup table，以实现更好的扩展性和维护性，同时保持良好的性能。

摘要由CSDN通过智能技术生成

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');

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.

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

解决方案

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

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

confusion: As you extend to more than A and P

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

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

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

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

Enum is not portable

On the plus side of using a single letter or Enum

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