(mysql.info) enum

Info Catalog

(mysql.info) blob

(mysql.info) string-types

(mysql.info) 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 strict SQL mode is enabled, attempts to insert invalid `ENUM'
      values result in an error.
 
    * 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'.
 
    * The term `index' here refers only to position within the list of
      enumeration values. It has nothing to do with table indexes.
 
 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.
 
 Trailing spaces are automatically deleted from `ENUM' member values in
 the table definition when a table is created.
 
 When retrieved, values stored into an `ENUM' column are displayed using
 the lettercase that was used in the column definition. Note that `ENUM'
 columns can be assigned a character set and collation. For binary or
 case-sensitive collations, lettercase is taken into account when
 assigning values to the column.
 
 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 does not work with `LOAD DATA', which treats all
 input as strings.) It is 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 CHAR)' 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 `Type' column of the output.
 

Info Catalog

(mysql.info) blob

(mysql.info) string-types

(mysql.info) set