Enum type w MySQLu

Jeśli kiedykolwiek używałeś w swojej bazie danych MySQL kolumn z typem ENUM, być może zauważyłeś kilka niemiłych rzeczy, których bez czytania dokumentacji nie spodziewałbyś się zobaczyć.

Przypuśćmy, że mamy następującą tak zdefiniowaną tabelę, z kolumnami typu enum:

mysql> show create table test_enum \G
*************************** 1. row ***************************
       Table: test_enum
Create Table: CREATE TABLE `test_enum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `e1` enum('Y','F','') DEFAULT NULL,
  `e2` enum('Y','F') NOT NULL DEFAULT 'Y',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Wstawmy kilka wierszy:

mysql> insert into test_enum (e1,e2) VALUES ('Y','Y'),('F','F'),('',''),(NULL,NULL),('N','N');
Query OK, 5 rows affected, 4 warnings (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 4

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'e2' at row 3 |
| Warning | 1048 | Column 'e2' cannot be null              |
| Warning | 1265 | Data truncated for column 'e1' at row 5 |
| Warning | 1265 | Data truncated for column 'e2' at row 5 |
+---------+------+-----------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from test_enum;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  1 | Y    | Y  |
|  2 | F    | F  |
|  3 |      |    |
|  4 | NULL |    |
|  5 |      |    |
+----+------+----+
5 rows in set (0.00 sec)

Zauważyliście coś ciekawego? Dwa pierwsze wpisy raczej nikogo nie dziwią - w końcu są poprawne. Jednak gdy przyjrzymy się trzem następnym, zauważymy że:

• insert stringa pustego '' do kolumny e2 powoduje wstawienie tej właśnie wartości, mimo iż nie ma jej zdefiniowanej jako wartość dozwolona,
• insert NULLa do kolumny e2 powoduje wstawienie pustego stringa mimo iż mamy zdefiniowaną wartość domyślną,
• insert wartości niepoprawnych powodują ustawienie na puste stringi.

Zanim omówię powyższe 'niespodzianki' wstawmy jeszcze dwa rekordy:

mysql> insert into test_enum (e1) VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_enum (id) VALUES (7);
Query OK, 1 row affected (0.05 sec)

mysql> select * from test_enum where id > 5;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  6 | NULL | Y  |
|  7 | NULL | Y  |
+----+------+----+
2 rows in set (0.00 sec)

Tym razem nasze dane są takie jakich byśmy się spodziewali.

Cechy typu ENUM, których być może się nie spodziewaliście:

• Typ ENUM jest obiektem, który definiuje wartości dozwolone w formie stringów, ale tak na prawdę wartość zapisywana jest przy pomocy indexów typu integer, zaczynając od 1. Weźmy pod uwagę kolumnę e1 naszej tabeli test_enum. Wartości ('Y','F','') są zapisywane jako (1,2,3). Poniżej przedstawię kilka przykładów SELECTów.
• Wartość 0 jest zarezerwowana dla pustego stringa - wartość błędu. 
• Wartość NULL jest zapisywana jako NULL lub wartość błędu (jeśli kolumna jest zdefiniowana jako NOT NULL).
• Jeśli zdfiniujemy enum jako ciagi znaków numerycznych, to wartości wstawiane do takiej kolumny są traktowane jako indexy dopuszczalnych wartości (zobacz poniższy przykład 2, dla tablicy test_enum2).

Przykład 1: Przykłady wyszukiwania wierszy po wartościach lub indexach dopuszczalnych wartości:

mysql> SELECT * FROM test_enum WHERE e1 = '';
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  3 |      |    |
|  5 |      |    |
+----+------+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_enum WHERE e1 = 0;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  5 |      |    |
+----+------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_enum WHERE e1 = 3;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  3 |      |    |
+----+------+----+
1 row in set (0.00 sec)

Przykład 2: Inny przykład tablicy z columną typu enum:

mysql> show create table test_enum2 \G
*************************** 1. row ***************************
       Table: test_enum2
Create Table: CREATE TABLE `test_enum2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `e1` enum('0','1','2','3') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test_enum2 (e1) VALUES (1),('1'),('4');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_enum2;
+----+------+
| id | e1   |
+----+------+
|  1 | 0    |
|  2 | 1    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

Przykład 3: Aby podejrzeć wartość string i jej index wystraczy:

mysql> select id, e1, e1+0 from test_enum2;
+----+------+------+
| id | e1   | e1+0 |
+----+------+------+
|  1 | 0    |    1 |
|  2 | 1    |    2 |
|  3 | 3    |    4 |
+----+------+------+
3 rows in set (0.00 sec)

Każdy nowy element wartości dopuszczalnej powinien być dopisany bez zmiany kolejności. W ten sposób nowa wartość będzie miała nadaną kolejną wartość indexu. W przeciwnym przypadku tablica będzieme musiała być przepisana aby starym wartościom nadać nowe indexy. Dla bardzo dużych tabel może być to czasochłonna opercja, a i chyba nie potrzebna.