Working with Database Structures - Learning MySQL - page 203

+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
Displaying the contents of the table, you can see that when you try to store anything
that's not in the valid values, an empty string is stored instead:
mysql> SELECT * FROM fruits_enum;
+------------+
| fruit_name |
+------------+
| Apple |
| |
| |
+------------+
3 rows in set (0.00 sec)
You can also specify a default value other than the empty string:
mysql> CREATE TABLE new_fruits_enum ( fruit_name ENUM('Apple', 'Orange', 'Pear')
-> DEFAULT 'Pear');
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO new_fruits_enum VALUES();
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM new_fruits_enum;
+------------+
| fruit_name |
+------------+
| Pear |
+------------+
1 row in set (0.00 sec)
Here, not specifying a value results in the default value Pear being stored.
SET(' value1 '[,' value2 '[, ...]])
A set of string values. A column of type SET can be set to zero or more values from
the list value1 , value2 , and so on, up to a maximum of 64 different values. While
the values are strings, what's stored in the database is an integer representation.
SET differs from ENUM in that each row can store only one ENUM value in a column,
but can store multiple SET values. This type is useful for storing a selection of
choices from a list, such as user preferences. Consider this example using fruit
names; the name can be any combination of the predefined values:
mysql> CREATE TABLE fruits_set ( fruit_name SET('Apple', 'Orange', 'Pear') );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO fruits_set VALUES ('Apple');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO fruits_set VALUES ('Banana');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+