MySQL
ENUM

Perché ENUM?

ENUM fornisce un modo per fornire un attributo per una riga. Gli attributi con un numero limitato di opzioni non numeriche funzionano meglio. Esempi:

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

I valori sono stringhe:

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

TINYINT come alternativa

Diciamo che abbiamo

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

Un'alternativa è

type TINYINT UNSIGNED

più

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

che è molto simile a una tabella molti-a-molti.

Confronto, e se migliore o peggiore di ENUM:

• (peggio) INSERISCI: è necessario cercare il type
• (peggio) SELEZIONA: è necessario unire per ottenere la stringa (ENUM ti dà la stringa senza sforzo)
• (migliore) Aggiunta di nuovi tipi: basta inserire in questa tabella. Con ENUM, è necessario eseguire una ALTER TABLE.
• (stessa) Ciascuna tecnica (per un massimo di 255 valori) richiede solo 1 byte.
• (misto) C'è anche un problema di integrità dei dati: TINYINT ammetterà valori non validi; mentre ENUM li imposta su un valore di stringa vuota speciale (a meno che non sia abilitata la modalità SQL rigorosa, nel qual caso vengono rifiutati). È possibile ottenere una migliore integrità dei dati con TINYINT rendendola una chiave esterna in una tabella di ricerca: con query / join appropriate, ma il costo per raggiungere l'altra tabella è ancora limitato. (Le FOREIGN KEYs non sono gratuite.)

VARCHAR come alternativa

Diciamo che abbiamo

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

Un'alternativa è

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

Questo è abbastanza aperto in quanto i nuovi tipi sono banalmente aggiunti.

Confronto, e se migliore o peggiore di ENUM:

• (stesso) INSERT: fornire semplicemente la stringa
• (peggio?) In INSERT un errore di battitura passerà inosservato
• (stesso) SELECT: viene restituita la stringa effettiva
• (peggio) Viene consumato molto più spazio

Aggiunta di una nuova opzione

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

Gli appunti

• Come in tutti i casi di MODIFY COLUMN, devi includere NOT NULL e qualsiasi altro qualificatore originariamente esistente, altrimenti andranno persi.
• Se si aggiunge alla fine dell'elenco e l'elenco è inferiore a 256 elementi, ALTER viene eseguito semplicemente cambiando lo schema. Questo non ci sarà una copia da tavolo lunga. (Le vecchie versioni di MySQL non avevano questa ottimizzazione.)

NULL vs NOT NULL

Esempi di ciò che accade quando NULL e 'bad-value' sono memorizzati in colonne nullable e not nullable. Mostra anche l'utilizzo del cast in numerico tramite +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)

Cosa c'è nella tabella dopo questi inserti. Questo utilizza "+0" per eseguire il cast a valori numerici per vedere cosa viene memorizzato.

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)