MySQL enums empty value

Question

I am trying to understand how enums could be used in mysql. If I insert anything to enum field that is out of the enum type -- mysql inserts empty string (with value 0).

fruit ENUM ('APPLE','BANANA','PEACH');
INSERT ... fruit='BANNANA'

Simple misspelling and MySQL inserts empty value, breaks database integrity and makes enums extremely useless.

CHECK constraints could help here but MySQL does not support them (pretty funny for "most popular" database in 2011)

The only way I see is to write the trigger to prevent empty string but it is too much work to write trigger for such a simple case.

Does there is a way to disable "empty string" MySQL behavior for enums?

Thanks

MySQL supports CHECK constraint since version 8.0.16 .

Liam
– Liam

2022-05-31 17:13:59 +00:00
Commented May 31, 2022 at 17:13 — Liam
– Liam, Commented May 31, 2022 at 17:13

Stephane Gosselin · Accepted Answer · 2011-11-17 21:52:59Z

As per the documentation:

If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0.

On a sidenote, I (and others) recommend you avoid enums, as they are not very efficient.

If you really need enums, consider using strict mode. Strict mode will at least throw an error when you try to insert an invalid value into an ENUM column. Otherwise only a warning is thrown and the value is simply set to an empty string ' ' (referenced internally as 0). Note: Errors can still be suppressed in strict mode if you use IGNORE.

Here is a link to the documentation on setting your MySQL server mode.

Happy coding!

Update: Nick's answer is one way to set your server in strict mode. TRADITIONAL mode is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, so keep in mind you will have additional data validation performed on other datatypes, such as DATE for example.

Nicola Cossu · Accepted Answer · 2011-11-17 21:38:14Z

6

Take a look at sql modes

SET SQL_MODE='TRADITIONAL';

answered Nov 17, 2011 at 21:38

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Collectives™ on Stack Overflow

MySQL enums empty value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related