Usage and difference of the set and enum types in mysql _ MySQL

Usage and difference of the set and enum types in mysql

Usage and difference of the set and enum types in mysql

In mysql, both enum and set are of the string type and can only be set,

The difference is that set can take multiple values, and enum can take only one value.

[php] CREATE TABLE `20121101_t` (      `id` int(11) NOT NULL AUTO_INCREMENT,      `name` varchar(20) NOT NULL,      `cl` set('x','w','r') NOT NULL,      `c2` enum('f','d') NOT NULL,      PRIMARY KEY (`id`)    ) ENGINE=InnoDB       insert into 20121101_t    values(null,'a.txt','r,w','d');      insert into 20121101_t    values(null,'b.txt','r,w','f');    

ENUM is a string object whose values are usually selected from a allowed value list. This list is explicitly listed in the column type description during table creation.

In some of the following cases, the value can also be an empty string ("") or NULL:

If an invalid value is inserted into an ENUM (that is, a string that is not in the allowed value list), the null string is inserted as a special error value. In fact, this string is different from a "normal" null string because it has a numeric index value of 0. More details will be provided later.

If an ENUM is declared as NULL, NULL is also a valid value of this column, and the default value of this column is also NULL. If an ENUM is declared as not null, the default value of this column will be the first member of the value allowed by this list.

Each enumerated value has an index value:

In the column description, the allowed member values of the list value are numbered from 1.

The index value of the null string error value is 0. This means that you can use the SELECT statement shown below to find the record rows that are assigned an invalid ENUM value.

Mysql> SELECT * FROM tbl_name WHERE enum_col = 0;

The index value of the NULL value is NULL.

For example, a column specified as ENUM ("one", "two", "three") can have any value shown below. The index value for each value is also as follows:

Value Index value

NULL

"" 0

"One" 1

"Two" 2

"Three" 3

When ''is found during the test, 0 indicates that no data is found.

You can change the enumerated values to a maximum of 65535 member values.

Starting from MySQL 3.23.51, when a table is created, spaces at the end of the ENUM value are automatically deleted.

When an ENUM column is assigned a value, the uppercase and lowercase letters are irrelevant. However, the case sensitivity of the values retrieved from the column later matches the allowed values specified during table creation.

If you retrieve an ENUM in a digital context, the index value of the column value is returned. For example, you can use numeric values to retrieve an ENUM column like this:

Mysql> SELECT enum_col + 0 FROM tbl_name;

If a number is stored in an ENUM, the number is treated as an index value and the stored value is the enumerated member corresponding to the index value. (However, this will not work in load data because it regards all input as strings .) It is unwise to store numbers in an ENUM string because it may disrupt thinking.

The ENUM value is ordered according to the list order in the column type description. (In other words, ENUM values are sorted by their index numbers .) For example, for ENUM ("a", "B") "a" after "B", but for ENUM ("B", ""), "B" is placed before ". Empty strings are placed before non-empty strings, and NULL values are placed before all other enumerated values. To prevent unexpected results, we recommend that you define the ENUM list in alphabetical order. You can also use group by concat (col) to determine which values are sorted alphabetically rather than indexed.

To obtain all possible values of an ENUM column, you can use show columns from table_name LIKE enum_colum

BitsCN.com