MySQL中的枚举是否需要为非NULL? [英] Does an enum in MySQL need to be NOT NULL?

问题描述

让我说:

ALTER TABLE asdf ADD field ENUM('Y', 'N') DEFAULT 'N';

是否在结尾处放一个NOT NULL，因为它只能是Y和N?

Is putting a NOT NULL on the end necessary as it can only be Y and N?

EDT:根据评论，如果我知道该软件始终将其设置为"N"或"Y"并进行了硬编码，那么可以将其保留还是可以以某种方式变为空.

EDT: based on comments, if I know the software always sets it to 'N' or 'Y' and is hardcoded in then is it OK to leave it off or could it still potentially become null some how.

推荐答案

如果您未在列定义中指定NOT NULL，MySQL将允许该值为NULL.

MySQL will allow the value to be NULL if you do not specify NOT NULL in the column definition.

这是一个快速测试:

mysql> create table test (id serial, field ENUM('Y','N') DEFAULT 'N');
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (field) VALUES ('Y');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (field) VALUES ('N');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (field) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (field) VALUES ('Invalid');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'field' at row 1 | 
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+-------+
| id | field |
+----+-------+
|  1 | Y     | 
|  2 | N     | 
|  3 | N     | 
|  4 | NULL  | 
|  5 |       | 
+----+-------+
5 rows in set (0.00 sec)

因此MySQL确实遵守默认值，但也允许NULL. (有趣的是，它将截断无效值并允许使用空白字符串，但这是一个不同的问题)

So MySQL does respect the default value, but also allows NULLs. (Interestingly, it will truncate invalid values and allow blank strings as well, but that's a different issue)

这篇关于MySQL中的枚举是否需要为非NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！