What are the different properties of the MySQL ENUM data type?

The MySQL ENUM type can be defined using the following properties that affect the allowed values ​​-

• NOT NULL -In the ENUM type , NULL values ​​are allowed by default. To disable NULL values, we need to use the NOT NULL attribute when describing the ENUM column.
• NULL - The NULL attribute is a synonym for DEFAULT NULL. The index value of NULL is NULL.
• DEFAULT - The DEFAULT attribute causes the ENUM data type to have a default value when no value is specified. In other words, we can say that the INSERT statement does not have to contain the value of the field because if it does not then the value following the DEFAULT will be inserted. DEFAULT Functions are not allowed in expressions. For the ENUM data type, DEFAULT values ​​include NULL and the empty string ('').

Example

The following example will demonstrate the use of these properties with the ENUM data type.

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> Create Table enumtesting(e_value ENUM('a','1')NOT NULL DEFAULT '1',id TINYINT NOT NULL);
Query OK, 0 rows affected (0.23 sec)

mysql>Insert into
enumtesting(e_value,id)values('1','1'),('',2),(NULL,3),('abc',4);
Query OK, 4 rows affected, 3 warnings (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 3

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code |  Message                                           |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'e_value' at row 2       |
| Warning | 1048 | Column 'e_value' cannot be null                    |
| Warning | 1265 | Data truncated for column 'e_value' at row 4       |
+---------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

mysql> Select * from enumtesting;
+----+---------+
| e_value | id |
+---------+----+
| 1       |  1 |
|         |  2 |
|         |  3 |
|         |  4 |
+---------+----+
4 rows in set (0.00 sec)

mysql> insert into enumtesting(id) values(5);
Query OK, 1 row affected (0.11 sec)

mysql> Select * from enumtesting;
+---------+----+
| e_value | id |
+---------+----+
| 1       | 1 |
|         | 2 |
|         | 3 |
|         | 4 |
| 1       | 5 |
+---------+----+
5 rows in set (0.00 sec)

mysql> select e_value, e_value+0 AS enum_index, id from enumtetsing;
+---------+------------+----+
| e_value | enum_index | id |
+---------+------------+----+
| 1       |          2 |   1|
|         |          0 |   2|
|         |          0 |   3|
|         |          0 |   4|
| 1       |          2 |   5|
+---------+------------+----+
5 rows in set (0.00 sec)

The above is the detailed content of What are the different properties of the MySQL ENUM data type?. For more information, please follow other related articles on the PHP Chinese website!