mysql set型 その2

先日のmysql set型 その1の続き。

前回を要約すると、set型の値更新のために、『更新前にSELECTで値を事前に取得→変更後の文字列を整形→整形した文字列でset型の値を変更するUPDATE』としているプログラムがあったが、『SELECTはしないでもUPDATE』はできるといった話。
前回は文字列関数で行ったが、set型の要素によっては上手いこと使えないケースもあるので、今回は別の方法でUPDATEを行う。

テーブルは先日と同じでデータは下記の通り。

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

set型は下記引用の通り数値を持っている。

MySQL SET 型
SET 型のカラムに数値を格納する場合、その数値のバイナリ表現に設定されたビットによって、カラム値のセット要素が決まります。たとえば、カラムが SET("a","b","c","d") として指定されているとします。この場合、セット要素は次のビット値を持ちます。
SET 要素 10 進数 2 進数
a 1 0001
b 2 0010
c 4 0100
d 8 1000

このカラムに値 9 を割り当てた場合、2 進数では 1001 になるため、SET 値の 1 番目と 4 番目の要素である "a" と "d" が選択され、結果の値は "a,d" になります。

数値を持つならcastしてみれば数値がわかる。

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

かがみは1（ツンデレ）+2（ツインテール）+16（双子）=19
つかさは8(貧乳）＋16(双子）=24
といった具合にset型の要素の最初のものから、1,2,4,8…の順に割り当てて計算できる。

ここまでくれば勘の良い人はわかると思うが、ビット演算だ。

みんながツインテールにした例では、attributeと2(ツインテール)のORをとれば良いわけだ。

mysql> update person set attribute = attribute | 2;
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 | つかさ | ツインテール,貧乳,双子       |
+----+-----------+----------------------------------------+

双子を外した例は下記の通り。attributeと16(双子)のANDをとったものと、attributeのXORをとる。ANDをとらないと、双子じゃないものには双子を追加、双子だったものには双子削除、といった結果になるので注意。

mysql> update person set attribute = attribute ^ (attribute & 16);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4  Changed: 2  Warnings: 0

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

この削除の方法はreplace関数による方法と違い、間違って他の要素を削除するということもない。また、replace関数で複数削除だと、replace関数を複数入れ子にする必要があるが、ビット演算ならその必要もない。

もちろんだが、追加と削除を同時に行うこともできる。データは初期状態に戻したテーブルに対して、ツインテール追加、双子と貧乳削除を行う。

mysql> update person set attribute = attribute ^ (attribute & 24) | 2;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

結論：set型の更新は文字列として扱うよりも、数値とみなしてビット演算で更新するのが吉。

※ツインテール追加の例で、事前にSELECT方式の場合、SELECT1発＋UPDATE3発と最低でもSQL4発は必要なのに対して、SELECTなし方式の場合、UPDATE1発なのでSQL1発。実際のシステムに適応して考えると更新対象は数千～数万だったりするので、事前にSELECT方式だとSQLの回数も膨大になる。SELECTあり、なしどちらが良いかは一目瞭然なので主眼に置かず。