選択肢型
データに格納する値をあらかじめ設定した特定の値だけに制限することができます。ENUM型は単一の値のみ、SET型は複数の値を選択することができます。
このENUM型は、サンプルデータのCountryLanguageテーブルで、「その言語がその国の公用語か」を表すIsOfficial列で使用されています。他にもCountryテーブルの「大陸名」を表すContinent列で使用されています。
mysql> SHOW CREATE TABLE CountryLanguage\G
*************************** 1. row ***************************
Table: CountryLanguage
Create Table: CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
あらかじめ設定した値以外の値を挿入更新すると、警告が表示され値は無視されます。"DEFAULT"値が設定されていれば"DEFAULT"値を、されていなければNULLが格納されます。
mysql> CREATE TABLE tf1 (TF ENUM('T','F'));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tf1 VALUES ('T');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tf1 VALUES ('Z');
Query OK, 1 row affected, 1 warning (0.01 sec)
Warning (Code 1265): Data truncated for column 'TF' at row 1
mysql> SELECT * FROM tf1;
+------+
| TF |
+------+
| T |
| |
+------+
2 rows in set (0.00 sec)
ENUM型は内部的には選択肢を番号に置き換えて格納しているため、値そのものでも割り当てられた番号でも参照、挿入などが可能です。
mysql> SELECT Code, Name, Continent FROM Country WHERE Continent = "Asia" LIMIT 2; +------+----------------------+-----------+ | Code | Name | Continent | +------+----------------------+-----------+ | AFG | Afghanistan | Asia | | ARE | United Arab Emirates | Asia | +------+----------------------+-----------+ 2 rows in set (0.00 sec) mysql> SELECT Code, Name, Continent FROM Country WHERE Continent = 2 LIMIT 2; +------+-------------+-----------+ | Code | Name | Continent | +------+-------------+-----------+ | NLD | Netherlands | Europe | | ALB | Albania | Europe | +------+-------------+-----------+ 2 rows in set (0.00 sec)
SET型は、値をカンマで区切って指定します。例えば、列の定義がSET('alpha', 'beta') NOT NULLとなっている場合、以下の4つの値を格納できます。
'' 'alpha' 'beta' 'alpha,beta'
カンマで区切って指定できる値は最大64個です。
ENUM型と同様にあらかじめ設定した値以外の値を挿入更新すると、警告が表示され値は無視されます。
mysql> CREATE TABLE s1 (id SERIAL, mc SET('a', 'b', 'c') NOT NULL);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO s1(mc) VALUES ('a'), ('a,c'), ('d');
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 1
Warning (Code 1265): Data truncated for column 'mc' at row 3
格納されている値の取得にはLIKEかFIND_IN_SET()関数を使用します。
mysql> SELECT * FROM s1 WHERE mc LIKE '%c%';
+----+-----+
| id | mc |
+----+-----+
| 2 | a,c |
+----+-----+
1 rows in set (0.00 sec)
mysql> SELECT * FROM s1 WHERE FIND_IN_SET('a', mc) > 0;
+----+-----+
| id | mc |
+----+-----+
| 1 | a |
| 2 | a,c |
+----+-----+
2 rows in set (0.00 sec)
定義された値はENUM型同様に数値で格納されていますが、各値が2進数で扱われています。ここの例では、'a'は1、'b'は2、'c'は4となっています。'a'と'c'が含まれる列は合計の5となります。
mysql> SELECT * FROM s1 WHERE mc = 5; +----+-----+ | id | mc | +----+-----+ | 2 | a,c | +----+-----+ 1 rows in set (0.00 sec)
列の定義が分かっている場合、ビット配列で該当する要素を1として検索することができます。以下では1要素目と3要素目を検索するためにb'101'としています。
mysql> SELECT * FROM s1 WHERE mc = CONVERT(b'101', UNSIGNED); +----+-----+ | id | mc | +----+-----+ | 2 | a,c | +----+-----+ 2 rows in set (0.00 sec)
