
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
what are the different attributes of MySQL ENUM data type?
MySQL ENUM types can be defined with following attributes that affect the allowed values −
- NOT NULL − In ENUM type, by default NULL values are allowed. To disallow NULL values, we need to use the NOT NULL attribute while describing the ENUM column.
- NULL − The NULL attribute is a synonym for DEFAULT NULL. The index value for NULL is NULL.
- DEFAULT − The DEFAULT attribute causes an ENUM data type to have a default value when a value is not specified. In other words, we can say that INSERT statement does not have to include a value for this field because if it does not include then the value following DEFAULT will be inserted. Functions are not allowed in the DEFAULT expression. For ENUM data type the DEFAULT values include NULL and empty string (‘’).
Example
Following example will show the use of these attributes with 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)
- Related Articles
- What is MySQL ENUM data type? What are the advantages to use ENUM data type?
- How can I insert default value in MySQL ENUM data type?
- Add a new value to a column of data type enum in MySQL?
- Does SQL Server have an equivalent to MySQL's ENUM data type?
- MySQL data types int versus enum?
- The equals and == operator for Enum data type in Java
- Explain attributes and the different types of attributes in DBMS?
- What are the different quote marks of MySQL?
- What is BLOB data type in MySQL?
- What is TEXT data type in MySQL?
- What should one use CHAR data type or VARCHAR data type in MySQL?
- What is the data type for unix_timestamp in MySQL?
- Attributes of Data Warehouse
- Do MySQL Can Enum type values contain spaces in it?
- Count items in a MySQL table with type ENUM involved?

Advertisements