Contents
How is the enum data type used in MySQL?
In MySQL, an ENUM is a string object whose value is chosen from a list of permitted values defined at the time of column creation. The ENUM data type provides the following advantages: Compact data storage. MySQL ENUM uses numeric indexes (1, 2, 3, …) to represents string values.
When to use an enum column instead of an index?
If the numeric value is quoted, it is still interpreted as an index if there is no matching string in the list of enumeration values. For these reasons, it is not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing.
What happens if you insert an invalid enum in MySQL?
See the following statement: In the non-strict SQL mode, if you insert an invalid value into an ENUM column, MySQL will use an empty string ” with the numeric index 0 for inserting. In case the strict SQL mode is enabled, trying to insert an invalid ENUM value will result in an error.
What is the index of the empty string in MySQL?
The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned: The index of the NULL value is NULL . The term “index” here refers to a position within the list of enumeration values. It has nothing to do with table indexes.
What are the advantages of the enum data type?
The ENUM data type provides the following advantages: 1 Compact data storage. MySQL ENUM uses numeric indexes (1, 2, 3, …) to represents string values. 2 Readable queries and output. More
Which is enum value must be quoted string literal?
An enumeration value must be a quoted string literal. For example, you can create a table with an ENUM column like this: Inserting 1 million rows into this table with a value of ‘medium’ would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string ‘medium’ in a VARCHAR column.
What happens if an enum column is declared not null?
If an ENUM column is declared to permit NULL, the NULL value is a valid value for the column, and the default value is . If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.
How to insert data into an enum column?
To insert data into an ENUM column, you use the enumeration values in the predefined list. For example, the following statement inserts a new row into the tickets table. Besides the enumeration values, you can use the numeric index of the enumeration member for inserting data into an ENUM column.