Is it wrong to use enum ('y', 'n') instead of a boolean field in a MySQL table?

Question

So, a few years ago I saw a system database schema developed by a third party, and noticed that they used enum ('y', 'n') instead of a boolean (tinyint) field. I don’t know why, but I loved him so much, I found that reading was easier (completely subjective, I know), but I accepted it and started using it since then. I suppose I could change it to "true" and "false", but what I can say, I just liked it.

Now, when they say, are there any failures in doing it this way - except maybe a little annoying to the programmer who came at the end of the game?

Answer 1

Yes this is bad. You lose the intuitive logic with it ( SELECT * FROM user WHERE NOT banned becomes SELECT * FROM user WHERE banned = 'n' ), and you get strings instead of logical elements on the side of your application, so your Boolean conditions also become cumbersome. Other people who work with your circuit will be bitten by seeing the names of columns similar to flags and try to use logical logic on them.

Answer 2

As explained in the manual :

If you enter an invalid value in ENUM (that is, a string that is not in the list of acceptable values), an empty string will be inserted instead as a special error value. This line may differ from the “normal” empty line by the fact that the line has a numeric value of 0. See Section 11.4.4, “Index Values ​​for Enumeration Literals” for more information about numeric indexes for enumeration values.

If strict SQL mode is enabled, attempts to insert invalid ENUM values ​​result in an error.

In this regard, a ENUM leads to a behavior of type BOOLEAN ; otherwise, I tend to agree with the @lanzz answer , which makes integration with one application, which is slightly less direct.

Answer 3

One factor to consider is whether the people who wrote the source schema restrict MySQL or not. If it is intended only for work in MySQL, then adaptation to MySQL makes sense. If the same schema is intended for use with other DBMSs, then a more general schema design that works in all relevant DBMSs may be better for people creating the design.

With that said, enum has moderate MySQL specifics, but something that is equivalent to enum can easily be created in another DBMS:

 CREATE TABLE ... ( ... FlagColumn CHAR(1) NOT NULL CHECK(FlagColumn IN ('y', 'n')), ... ); 

The way that different DBMSs handle BOOLEAN is not as unified as you would like, SQL Standard, despite this (and the reason, as always, is history, less compatible systems had a variation on BOOLEAN before the standard did, and changing their implementation violates code of their existing customers).

So, I would not automatically blame the use of enum over boolean , but it is better to use boolean for boolean flags.

Answer 4

I think this is fashionable - just use the boolean type - what it is.

But suppose he leaves the door open for “fuzzy” meanings, such as “maybe” and “maybe.”