mysql set型 その1

先日、set型の更新に一度SELECTでset型のcolumnの値を取得してから、更新用の文字列を生成している後輩がいた。話を聞いてみると、既存の設定された要素は残しつつ、要素を追加、削除するためらしい。SELECTで取得した値をカンマで分けて各要素をみて、削除要素を含んでいれば削除し、追加要素を含んでいなければ追加する。そうして更新後の必要な要素が分かったら、各要素をカンマでつなぎ文字列を生成する。

正直、このSELECTは必要ない。SELECTしなくても上手く更新できる。

例として次のテーブルとデータを考えよう。

CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`attribute` set('ツンデレ','ツインテール','眼鏡','貧乳','双子') NOT NULL,
PRIMARY KEY (`id`)
);

mysql> select * from person;
+----+-----------+----------------------------------------+
| id | name      | attribute                              |
+----+-----------+----------------------------------------+
|  1 | こなた | 貧乳                                 |
|  2 | かがみ | ツンデレ,ツインテール,双子 |
|  3 | みゆき | 眼鏡                                 |
|  4 | つかさ | 貧乳,双子                          |
+----+-----------+----------------------------------------+

まずは追加を考える。全員がツインテールにしたとしよう。もちろん他の属性要素はそのまま残る。どう更新するか。

mysql> update person set attribute = concat(attribute, ',ツインテール');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> select * from person;
+----+-----------+----------------------------------------+
| id | name      | attribute                              |
+----+-----------+----------------------------------------+
|  1 | こなた | ツインテール,貧乳              |
|  2 | かがみ | ツンデレ,ツインテール,双子 |
|  3 | みゆき | ツインテール,眼鏡              |
|  4 | つかさ | ツインテール,貧乳,双子       |
+----+-----------+----------------------------------------+

set型は要素の順番は関係ないので、concat関数で最後にツインテールという要素をつなげてやれば上手く更新できる。同じ要素が入っていても良いので元からツインテールのかがみも問題無し。要素の前にカンマを入れるのがポイント。

引用元
SET 要素を複数持つ値では、値の挿入時には、要素を列記する順序は重要ではありません。値の中で特定の要素を列記する回数も重要ではありません。 その後、値を取り出すときには、値内の各要素は 1 回のみ出現し、テーブルの作成時に指定された順序でそれぞれの要素が列記されます。たとえば、カラムが SET("a","b","c","d") として指定されている場合、このカラムの値を取り出したときには、"a,d"、"d,a"、"d,a,a,d,d" はいずれも "a,d" として表現されます。

追加は文字列関数でなんとかできた。削除もreplace関数で文字列を除去すればできるように思える。

mysql> update person set attribute = replace(attribute, '双子', '');
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Rows matched: 4  Changed: 2  Warnings: 2

mysql> select * from person;
+----+-----------+---------------------------------+
| id | name      | attribute                       |
+----+-----------+---------------------------------+
|  1 | こなた | ツインテール,貧乳       |
|  2 | かがみ | ツンデレ,ツインテール |
|  3 | みゆき | ツインテール,眼鏡       |
|  4 | つかさ | ツインテール,貧乳       |
+----+-----------+---------------------------------+

しかし、これはあくまで文字列の削除なので、双子座と言う要素があった場合は、双子だけでなく、双子座も要素から外れてしまう。仮に要素を上手いこと問題なく設定していたとしても複数削除の場合は、replace関数を入れ子にしてやる必要があるので記述が手間である。

不可能ではないかもしれないが、set型を文字列として扱い、文字列関数で上手く更新するのは難しそうだ。どのように扱えば良いかは、また今度。