MySQL
ENUM

¿Por qué ENUM?

ENUM proporciona una forma de proporcionar un atributo para una fila. Los atributos con un pequeño número de opciones no numéricas funcionan mejor. Ejemplos:

reply ENUM('yes', 'no')
gender ENUM('male', 'female', 'other', 'decline-to-state')

Los valores son cadenas:

INSERT ... VALUES ('yes', 'female')
SELECT ... --> yes female

TINYINT como alternativa

Digamos que tenemos

type ENUM('fish','mammal','bird')

Una alternativa es

type TINYINT UNSIGNED

más

CREATE TABLE AnimalTypes (
    type TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL  COMMENT "('fish','mammal','bird')",
    PRIMARY KEY(type),
    INDEX(name)
) ENGINE=InnoDB

que es muy parecido a una tabla de muchos a muchos.

Comparación, y si es mejor o peor que ENUM:

• (peor) INSERTAR: hay que buscar el type
• (peor) SELECCIONE: necesita UNIRSE para obtener la cadena (ENUM le da la cadena sin esfuerzo)
• (mejor) Agregando nuevos tipos: Simplemente insértelos en esta tabla. Con ENUM, necesitas hacer una ALTER TABLE.
• (igual) Cualquiera de las dos técnicas (para hasta 255 valores) toma solo 1 byte.
• (mixto) También hay un problema de integridad de datos: TINYINT admitirá valores no válidos; mientras que ENUM establece en un valor especial de cadena vacía (a menos que se habilite el modo SQL estricto, en cuyo caso se rechazan). Se puede lograr una mejor integridad de los datos con TINYINT al convertirla en una clave externa en una tabla de búsqueda: que, con las consultas / combinaciones apropiadas, pero aún existe el pequeño costo de llegar a la otra tabla. ( FOREIGN KEYs no son gratuitas).

VARCHAR como alternativa

Digamos que tenemos

type ENUM('fish','mammal','bird')

Una alternativa es

type VARCHAR(20)  COMENT "fish, bird, etc"

Esto es bastante abierto ya que los nuevos tipos se agregan de forma trivial.

Comparación, y si es mejor o peor que ENUM:

• (mismo) INSERT: simplemente proporciona la cadena
• (¿peor?) En INSERTAR un error tipográfico pasará desapercibido
• (igual) SELECCIONAR: se devuelve la cadena real
• (Peor) Se consume mucho más espacio.

Añadiendo una nueva opción

ALTER TABLE tbl MODIFY COLUMN type ENUM('fish','mammal','bird','insect');

Notas

• Al igual que con todos los casos de MODIFICAR COLUMNA, debe incluir NOT NULL , y cualquier otro calificador que haya existido originalmente, de lo contrario, se perderán.
• Si agrega al final de la lista y la lista está en 256 elementos, ALTER se realiza simplemente cambiando el esquema. Es decir, no habrá una copia larga de la tabla. (Las versiones anteriores de MySQL no tenían esta optimización.)

NULL vs NOT NULL

Ejemplos de lo que sucede cuando NULL y 'mal valor' se almacenan en columnas que admiten nulos y que no admiten nulos. También muestra el uso de casting a numérico a través de +0 .

CREATE TABLE enum (
    e     ENUM('yes', 'no')   NOT NULL,
    enull ENUM('x', 'y', 'z')     NULL
        );
INSERT INTO enum (e, enull)
    VALUES
        ('yes', 'x'),
        ('no',  'y'),
        (NULL,  NULL),
        ('bad-value', 'bad-value');
Query OK, 4 rows affected, 3 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 3

mysql>SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1048 | Column 'e' cannot be null                  | 
| Warning | 1265 | Data truncated for column 'e' at row 4     |
| Warning | 1265 | Data truncated for column 'enull' at row 4 |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)

Lo que está en la tabla después de esas inserciones. Esto usa "+0" para convertir para ver numéricamente lo que está almacenado.

mysql>SELECT e, e+0 FROM enum;
+-----+-----+
| e   | e+0 |
+-----+-----+
| yes |   1 |
| no  |   2 |
|     |   0 |  -- NULL
|     |   0 |  -- 'bad-value'
+-----+-----+
4 rows in set (0.00 sec)

mysql>SELECT enull, enull+0 FROM enum;
+-------+---------+
| enull | enull+0 |
+-------+---------+
| x     |       1 |
| y     |       2 |
| NULL  |    NULL |
|       |       0 |  -- 'bad-value'
+-------+---------+
4 rows in set (0.00 sec)