mysql enum 为空查询_MySQL中的枚举是否需要为非NULL？

Lets say I have this:

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

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

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.

解决方案

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

Here's a quick test:

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)

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)