MySQL
ENUM

Pourquoi ENUM?

ENUM fournit un moyen de fournir un attribut pour une ligne. Les attributs avec un petit nombre d'options non numériques fonctionnent mieux. Exemples:

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

Les valeurs sont des chaînes:

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

TINYINT comme alternative

Disons que nous avons

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

Une alternative est

type TINYINT UNSIGNED

plus

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

ce qui ressemble beaucoup à une table plusieurs-à-plusieurs.

Comparaison, et meilleure ou pire que ENUM:

• (pire) INSERT: besoin de rechercher le type
• (pire) SELECT: besoin de JOINDRE pour obtenir la chaîne (ENUM vous donne la chaîne sans effort)
• (mieux) Ajouter de nouveaux types: insérez simplement dans ce tableau. Avec ENUM, vous devez faire un ALTER TABLE.
• (idem) Chaque technique (pour 255 valeurs maximum) ne prend que 1 octet.
• (mixed) Il y a aussi un problème d'intégrité des données: TINYINT admettra des valeurs non valides; alors que ENUM leur ENUM une valeur de chaîne vide spéciale (sauf si le mode SQL strict est activé, auquel cas ils sont rejetés). Une meilleure intégrité des données peut être obtenue avec TINYINT en faisant une clé étrangère dans une table de recherche: ce qui, avec les requêtes / jointures appropriées, présente néanmoins un faible coût pour atteindre l'autre table. (Les FOREIGN KEYs ne sont pas gratuites.)

VARCHAR comme alternative

Disons que nous avons

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

Une alternative est

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

Ceci est assez ouvert dans la mesure où de nouveaux types sont trivialement ajoutés.

Comparaison, et meilleure ou pire que ENUM:

• (même) INSERT: fournissez simplement la chaîne
• (pire?) Sur INSERT une faute de frappe passera inaperçue
• (même) SELECT: la chaîne actuelle est renvoyée
• (pire) Beaucoup plus d'espace est consommé

Ajouter une nouvelle option

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

Remarques

• Comme avec tous les cas de MODIFY COLUMN, vous devez inclure NOT NULL et tous les autres qualificatifs qui existaient à l'origine, sinon ils seront perdus.
• Si vous ajoutez à la fin de la liste et que la liste contient moins de 256 éléments, le programme ALTER se fait simplement en modifiant le schéma. C'est-à-dire qu'il n'y aura pas de copie longue de la table. (Les anciennes versions de MySQL n'avaient pas cette optimisation.)

NULL vs NOT NULL

Des exemples de ce qui se passe lorsque NULL et «bad-value» sont stockés dans des colonnes nullables et non nullables. Affiche également l'utilisation de la conversion en numérique via +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)

Ce qui est dans la table après ces insertions. Cela utilise "+0" pour afficher numériquement ce qui est stocké.

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)