ENUM Data Type

Description

The ENUM type is defined as the enumerated string constants. Only the specified string elements are allowed as the value of the column defined as ENUM and the maximum number of the ENUM elements is 65535. In the column of the ENUM type, each value is saved as 1 byte when the number of the ENUM elements is less than 256 and 2 bytes when the number is 256 or more. ENUM value allows numeric data type or string type.

ENUM type column is handled as a number and considered as an index number value, which corresponds to the ENUM type when the compared value is CHAR/VARCHAR in the query.

Syntax

<enum_type>

    : ENUM '(' <char_string_literal_list> ')'

<char_string_literal_list>

    : <char_string_literal_list> ',' CHAR_STRING

    | CHAR_STRING

Example

The following example shows the definition of the ENUM column.

CREATE TABLE tbl (

    color ENUM('red', 'yellow', 'blue')

);

The color column can have one of following values:

Value	Index Number
NULL	NULL
'red'	1
'yellow'	2
'blue'	3

The following example shows the insertion of a value into the ENUM column.

INSERT into tbl values ('yellow'), ('red'), (2), ('blue');

The following example shows the SELECT statement that retrieves the ENUM column where the value has been inserted in the above.

SELECT color FROM tbl;

 

  color

======================

  yellow

  red

  yellow

  blue

 

SELECT color FROM tbl ORDER BY color ASC;

 

  color

======================

  red

  yellow

  yellow

  blue

 

SELECT color FROM tbl ORDER BY cast(color as char) ASC;

 

  color

======================

  blue

  red

  yellow

  yellow

Characteristics

• When the string context is used as the ENUM value, the string is returned. The following example shows a case of using the string context.

• SELECT CONCAT(enum_col, 'color') FROM tbl_name;

   

    CONCAT(color, '_color')

  ======================

    yellow_color

    red_color

    yellow_color

    blue_color

• When the numeric context is used as the ENUM value, the index number is returned. The numeric value can be searched on the ENUM column as follows.

• SELECT color + 0 FROM tb;

   

    color + 0

  ======================

    2

    1

    2

    3

• The result of using the string is different from the result of using the index number. See the following example.

• -- will use the ENUM index value because it is compared with a number

  SELECT color FROM tbl WHERE color <= 1;

   

    color

  ======================

  red

   

   

  -- will use the ENUM char literal value because it is compared with a CHAR type

  SELECT color FROM tbl WHERE color <= 'red';

   

    color

  ======================

  red

  blue

• Index scan of the ENUM type column is allowed for = and IN operators. Index scan cannot process any other comparison operators.
• The value which exceeds the range that the ENUM type can express is not converted to the ENUM type and an error occurs. For the error data, automatic mapping to the default index value (0) and the default string value (NULL) is not supported.
• When a number inserted in the ENUM type column is enclosed within single quotes (' '), if the value is included in the list of the ENUM elements, the value is interpreted as a string value; otherwise, it is interpreted as an index number. Therefore, to avoid confusion, we recommend that you do not use the value similar to the number as the ENUM element value. The following example shows typing an ENUM element value similar to a number in the ENUM type column.

  CREATE TABLE tb2 (nums enum('0', '1', '2'));

  INSERT INTO tb2 (nums) VALUES(1),('1'),('3');

  SELECT * FROM tb2;

   

    nums

  ======================

    0

    1

    2

  • If the entered 1 is not enclosed within single quotes, 0 (corresponds to the Index Number 1) is inserted instead of 1.
  • When '1' is entered, '1' value is inserted since the corresponding ENUM element value exists.
  • When '3' is entered, '2' (corresponds to the Index Number 3) is inserted because there is no corresponding ENUM element value and 3 is a valid index number.
• The ENUM values are sorted by the index number, not by the string value of the element. NULL values are sorted on the front of all strings and blank strings are sorted on the front of any other strings. To sort elements in alphabetic order in the ENUM type column, use the CAST function as follows.

• SELECT color FROM tb ORDER BY cast(color as char) ASC;

• When converting the ENUM type to the other type, the index number or the string of the ENUM type is converted according to the target type. In the following table, the types with an asterisk (*) can be converted to the ENUM type.

Type	Value (Index Number/String)
*SHORT	Index Number
*INTEGER	Index Number
*BIGINT	Index Number
*FLOAT	Index Number
*DOUBLE	Index Number
*NUMERIC	Index Number
*MONETARY	Index Number
*TIME	String
*DATE	String
*DATETIME	String
*TIMESTAMP	String
*CHAR	String
*VARCHAR	String
BIT	String
VARBIT	String

Note

• To view all values allowed for the ENUM column, use SHOW COLUMNS.
• Each ENUM value has its index number based on the order of the sorting elements. The element index number starts at 1.
• Blank strings can be used as an ENUM element value when operators have explicitly specified a general index number to the blank strings.
• If operators have not specified a general index number to the blank strings, the index number of blank strings is 0. To search the rows with the blank strings, use the following sentence.

• SELECT * FROM tb WHERE color=0;

• In the ENUM column declared to allow NULL, the index number for NULL is NULL.
• The default value of the column, which allows NULL is NULL. For NOT NULL, the default value of the column is the first element of the ENUM list specified while defining the column.
• When a table is created, all trailing blanks of all elements in the ENUM column are automatically removed.
• The cases of the ENUM element are not changed but the cases defined while defining the column are maintained as they are.
• For the operation where operands are Type 1 and Type 2, the result type is as follows. The exception of the following rule is the case of comparing the ENUM column to the constant value. In this case, the constant value is changed to the ENUM value of the same type.

Type 1	Type 2	Result Type
SHORT	ENUM	SHORT
INTEGER	ENUM	INTEGER
BIGINT	ENUM	BIGINT
FLOAT	ENUM	FLOAT
DOUBLE	ENUM	DOUBLE
NUMERIC	ENUM	NUMERIC
MONETARY	ENUM	MONETARY
TIME	ENUM	TIME
DATE	ENUM	DATE
DATETIME	ENUM	DATETIME
TIMESTAMP	ENUM	TIMESTAMP
CHAR	ENUM	CHAR
VARCHAR	ENUM	VARCHAR

Using ENUM Type at the Driver Level

The ENUM type is not specially mapped to various drivers such as JDBC and CCI. Therefore, application developers can use the STRING type as they have used. The following example shows the JDBC application.

Statement stmt = connection.createStatement(¡°SELECT color FROM tbl¡±);

ResultSet rs = stmt.executeQuery();

while(rs.next()){

   System.out.println(rs.getString());

}

The following example shows the CCI application.

req_id = cci_prepare (conn, ¡°SELECT color FROM tbl¡±, 0, &err);

error = cci_execute (req_id, 0, 0, &err);

if (error < CCI_ER_NO_ERROR)

{

    /* handle error */

}

error = cci_cursor (req_id, 1, CCI_CURSOR_CURRENT, &err);

if (error < CCI_ER_NO_ERROR)

{

    /* handle error */

}

error = cci_fetch (req_id, &err);

if (error < CCI_ER_NO_ERROR)

{

    /* handle error */

}

cci_get_data (req, idx, CCI_A_TYPE_STR, &data, 1);

Constraints

• The ENUM type column does not allow the DEFALUT value.
• No expressions can be used for the ENUM value. For example, the following CREATE TABLE statement returns an error.

  CREATE TABLE tb (

      color ENUM('red', CONCAT('light ','gray'), 'blue')

  );