(mysql.info.gz) ENUM

Info Catalog

(mysql.info.gz) BLOB

(mysql.info.gz) String types

(mysql.info.gz) SET

 
 11.4.4 The `ENUM' Type
 ----------------------
 
 An `ENUM' is a string object with a value chosen from a list of allowed
 values that are enumerated explicitly in the column specification at
 table creation time.
 
 The value may also be the empty string (`''') or `NULL' under certain
 circumstances:
 
    * If you insert an invalid value into an `ENUM' (that is, a string
      not present in the list of allowed values), the empty string is
      inserted instead as a special error value. This string can be
      distinguished from a "normal" empty string by the fact that this
      string has the numerical value 0. More about this later.
 
    * If an `ENUM' column is declared to allow `NULL', the `NULL' value
      is a legal value for the column, and the default value is `NULL'.
      If an `ENUM' column is declared `NOT NULL', its default value is
      the first element of the list of allowed values.
 
 Each enumeration value has an index:
 
    * Values from the list of allowable elements in the column
      specification are numbered beginning with 1.
 
    * 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:
 
           mysql> SELECT * FROM TBL_NAME WHERE ENUM_COL=0;
 
    * The index of the `NULL' value is `NULL'.
 
 For example, a column specified as `ENUM('one', 'two', 'three')' can
 have any of the values shown here.  The index of each value is also
 shown:
 
 *Value*     *Index*
 `NULL'      `NULL'
 `'''        0
 `'one''     1
 `'two''     2
 `'three''   3
 
 An enumeration can have a maximum of 65,535 elements.
 
 Starting from MySQL 3.23.51, trailing spaces are automatically deleted
 from `ENUM' member values when the table is created.
 
 Lettercase is irrelevant when you assign values to an `ENUM' column.
 However, values retrieved from the column later are displayed using the
 lettercase that was used in the column definition.
 
 If you retrieve an `ENUM' value in a numeric context, the column value's
 index is returned.  For example, you can retrieve numeric values from
 an `ENUM' column like this:
 
      mysql> SELECT ENUM_COL+0 FROM TBL_NAME;
 
 If you store a number into an `ENUM' column, the number is treated as an
 index, and the value stored is the enumeration member with that index.
 (However, this will not work with `LOAD DATA', which treats all input
 as strings.)  It's not advisable to define an `ENUM' column with
 enumeration values that look like numbers, because this can easily
 become confusing. For example, the following column has enumeration
 members with string values of `'0'', `'1'', and `'2'', but numeric
 index values of `1', `2', and `3':
 
      numbers ENUM('0','1','2')
 
 `ENUM' values are sorted according to the order in which the enumeration
 members were listed in the column specification.  (In other words,
 `ENUM' values are sorted according to their index numbers.)  For
 example, `'a'' sorts before `'b'' for `ENUM('a', 'b')', but `'b'' sorts
 before `'a'' for `ENUM('b', 'a')'.  The empty string sorts before
 non-empty strings, and `NULL' values sort before all other enumeration
 values.  To prevent unexpected results, specify the `ENUM' list in
 alphabetical order. You can also use `GROUP BY CAST(col AS VARCHAR)' or
 `GROUP BY CONCAT(col)' to make sure that the column is sorted lexically
 rather than by index number.
 
 If you want to determine all possible values for an `ENUM' column, use
 `SHOW COLUMNS FROM TBL_NAME LIKE ENUM_COL' and parse the `ENUM'
 definition in the second column of the output.
 

Info Catalog

(mysql.info.gz) BLOB

(mysql.info.gz) String types

(mysql.info.gz) SET