enum vs boolean in case of multiple yes/no fields

Question

I want to store information from a form where there are multiple yes/No question. For those how should I create table fields? I have experienced mysql doesn't support multiple columns to have same ENUM values. So I can't assign 'yes','no' for more than one column.

If I use boolean? how should I do it? Is it mandatory to have default values for boolean type field?

Answer 1

I don't like the enum datatype for various reasons.

Boolean in MySQL is actually a tinyint with 0 being false and 1 being true, or no and yes respectively.

BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:
mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false |
+------------------------+
mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true |
+------------------------+
mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true |
+------------------------+
However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:
mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true |
+--------------------------------+
mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true |
+-------------------------------+
mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false |
+-------------------------------+
mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false |
+--------------------------------+
The last two statements display the results shown because 2 is equal to neither 1 nor 0. 

Source.

FYI, this can be very useful for various other things. An example you can see in this answer I recently gave.

• ypercubeᵀᴹ: You may want to add the option for the BIT type, since the question is about multiple columns.