Table Column Types for Look Up Values

MySQL Tutorials - Herong's Tutorial Examples

∟Table Column Types for Different Types of Values

∟Table Column Types for Look Up Values

Describes 2 types of table columns to store look up values: ENUM and SET.

There are 2 types of table columns to store look up values: ENUM, SET.

"ENUM" - A look up value from a predefined list. ENUM actually stores the index of the value as an integer.

"SET" - A combination of look up values from a predefined list. SET actually uses 64 bits and their position to represent each look up value. So a SET can support only up to 64 values.

Examples of look up value column types, EnumSetColumns.sql:

-- EnumSetColumns.sql
-- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
--
CREATE TABLE EnumSet (
   Line VARCHAR(2),
   E ENUM('Mercury', 'Venus', 'Earth', 'Mars'),
   S SET('a','b','c','d')
   );
INSERT INTO EnumSet VALUES (
   '1',
   'Earth',
   'c'
   );
INSERT INTO EnumSet VALUES (
   '1',
   'Earth',
   'a,c'
   );

SELECT * FROM EnumSet;
SELECT Line, E+0, S+0 FROM EnumSet;

DROP TABLE EnumSet;

Running EnumSetColumns.sql on MySQL 8.0 server gives you:

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < EnumSetColumns.sql

Line    E       S
1       Earth   c
1       Earth   a,c

Line    E+0     S+0
1       3       4
1       3       5