MySQL枚举空值 [英] MySQL enums empty value

问题描述

我试图了解如何在mysql中使用枚举. 如果我在枚举字段中插入了超出枚举类型的任何内容，那么mysql将插入空字符串(值为0).

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).

• 水果ENUM(苹果"，香蕉"，桃子")；
• INSERT ... fruit ='BANNANA'

简单的拼写错误和MySQL插入空值，破坏数据库完整性并使枚举变得极为无用.

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

CHECK约束可以在这里提供帮助，但MySQL不支持它们(对于2011年最受欢迎"的数据库而言，这很有趣)

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.

有没有一种方法可以为枚举禁用空字符串" MySQL行为?

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

谢谢

推荐答案

根据文档:

如果将无效值插入到ENUM中(即，在允许值列表中不存在字符串)，则会插入空字符串作为特殊错误值.通过将该字符串的数值设置为0，可以将该字符串与正常"空字符串区分开.

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.

如果您确实需要枚举，请考虑使用严格模式.当您尝试在ENUM列中插入无效值时，严格模式至少会引发错误.否则，只会引发警告，并且该值将被简单地设置为空字符串''(内部引用为0).注意:如果使用IGNORE，在严格模式下仍然可以抑制错误.

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.

此处是指向设置MySQL服务器模式的文档的链接.

祝您编程愉快！

更新:Nick的回答是将服务器设置为严格模式的一种方法.传统模式等效于STRICT_TRANS_TABLES，STRICT_ALL_TABLES，NO_ZERO_IN_DATE，NO_ZERO_DATE，ERROR_FOR_DIVISION_BY_ZERO，NO_AUTO_CREATE_USER，因此请记住，您将对其他数据类型(例如DATE)执行附加数据验证.

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.

这篇关于MySQL枚举空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！