inoue 12:28 PM
データベースのテーブルのID列にちょっとした意味を持たせ、ビット演算を使ってすっきりと条件抽出をする小技をご紹介します。
例題として、次のようなデータベースを考えます。
- ユーザ情報とサービス情報のテーブルがあり、ユーザ情報には各々の利用サービス情報が登録されている。
- サービスは3タイプ（A、B、C）×3期間の計9種類が存在する。
一般的な設計方法
通常、テーブルのID列を整数型にし、1から順に一意のID番号を振っていくようにするのがデータベース設計の基本であり、もっとも簡単な方法になります。
ユーザ情報テーブル users +----+-----------------------+ | id | name | service_id | +----+-----------------------+ | 1 | sakai | 1 | | 2 | kimoto | 2 | | 3 | ishida | 3 | | 4 | nagai | 4 | | 5 | toyama | 5 | | 6 | tanaka | 6 | | 7 | ichikawa | 7 | | 8 | nakayama | 8 | | 9 | inoue | 9 | +----+----------+------------+ サービス情報テーブル services +----+---------------------+------+------+ | id | name | type | term | +----+---------------------+------+------+ | 1 | Service A, 1 week | A | 1w | | 2 | Service A, 4 weeks | A | 4w | | 3 | Service A, 3 months | A | 3m | | 4 | Service B, 1 week | B | 1w | | 5 | Service B, 4 weeks | B | 4w | | 6 | Service B, 3 months | B | 3m | | 7 | Service C, 1 week | C | 1w | | 8 | Service C, 4 weeks | C | 4w | | 9 | Service C, 3 months | C | 3m | +----+---------------------+------+------+
例題として次の2問のSQLクエリを作成してみました。
【問1】Aタイプのサービス利用者を抽出しなさい。
SELECT u.name
FROM users u, services s
WHERE u.service_id = s.id
AND s.type = 'A';
【問2】3ヶ月のサービス利用者を抽出しなさい。
SELECT u.name
FROM users u, services s
WHERE u.service_id = s.id
AND s.term = '3m';
サービスの種類を条件にしたユーザの抽出を行うには、ユーザ情報とサービス情報2つのテーブルを結合したSQLクエリを作成する必要があります。
この例のような簡単なテーブル同士の結合ですと、何ら問題ないのですが、実際のデータベースシステムでは、この結合がどんどん複雑化していってしまうことが多いです。
また、システムの規模が大きくなって、データベースサーバを分割した際などは、この
テーブル結合ができなくなるので、プログラム側での工夫が必要になります。
ビット演算の利用を意識した設計方法
次に、ID列の数値をビット演算で利用できるようにした場合を考えます。
ユーザ情報テーブル users +----+-----------------------+ | id | name | service_id | +----+-----------------------+ | 1 | sakai | 17 | | 2 | kimoto | 18 | | 3 | ishida | 20 | | 4 | nagai | 33 | | 5 | toyama | 34 | | 6 | tanaka | 36 | | 7 | ichikawa | 65 | | 8 | nakayama | 66 | | 9 | inoue | 68 | +----+----------+------------+ サービス情報テーブル services +------+---------------------+------+------+ | id | name | type | term | +------+---------------------+------+------+ | 17 | Service A, 1 week | A | 1w | | 18 | Service A, 4 weeks | A | 4w | | 20 | Service A, 3 months | A | 3m | | 33 | Service B, 1 week | B | 1w | | 34 | Service B, 4 weeks | B | 4w | | 36 | Service B, 3 months | B | 3m | | 65 | Service C, 1 week | C | 1w | | 66 | Service C, 4 weeks | C | 4w | | 68 | Service C, 3 months | C | 3m | +------+---------------------+------+------+
例題の答えは次のようになります。
【問1】Aタイプのサービス利用者を抽出しなさい。
SELECT u.name
FROM users u
WHERE u.service_id & 16;
【問2】3ヶ月のサービス利用者を抽出しなさい。
SELECT u.name
FROM users u
WHERE u.service_id & 4;
サービス情報を結合することなく、ユーザ情報テーブルだけで抽出が行えることがわかるかと思います。
ここでは、サービス情報のID列の値のビットごとに次のような意味を与えています。
1（2の0乗）＝ 0000 0001 ＝ 1週間 2（2の1乗）＝ 0000 0010 ＝ 4週間 4（2の2乗）＝ 0000 0100 ＝ 3ヶ月 16（2の4乗）＝ 0001 0000 ＝ Aタイプ 32（2の5乗）＝ 0010 0000 ＝ Bタイプ 64（2の6乗）＝ 0100 0000 ＝ Cタイプ
ID番号の値は次のように決めています。
Service A, 1 week: 16 + 1 = 17 Service C, 3 months: 64 + 4 = 68
数値でこのように見ててもよくわからないのですが、これを2進数に変換すると理解しやすいかと思います。
Service A, 1 week: 0001 0000 + 0000 0001 ----------- 0001 0001 => 17 Service C, 3 months: 0100 0000 + 0000 0100 ----------- 0100 0100 => 68
service_idの値と抽出条件との論理積（AND）をとることで、ユーザ情報とサービス情報のテーブルを結合することなく、サービスの種類でのユーザ抽出が可能になります。
論理積はかける数と同じビットが1となっている場合正となります。
問1と問2のWHERE句を使ってサービス情報テーブルで次のクエリを実行すると、抽出したいサービスのみがくくられている様子がよくわかると思います。
> select * from services where id & 16;
+------+---------------------+
| id | name |
+------+---------------------+
| 17 | Service A, 1 week |17 = 0001 0001
| 18 | Service A, 4 weeks |18 = 0001 0010
| 20 | Service A, 3 months |20 = 0001 0100
+------+---------------------+
16 = 0001 0000
> select * from services where id & 4;
+------+---------------------+
| id | name |
+------+---------------------+
| 20 | Service A, 3 months |20 = 0001 0100
| 36 | Service B, 3 months |36 = 0010 0100
| 68 | Service C, 3 months |36 = 0100 0100
+------+---------------------+
4 = 0000 0100
ここまでは、データベース側について話してまいりましたが、呼び出し側になるPHPにおいても同様にプログラミングが可能です。
たとえば、全ユーザ情報を取得し、サービスの期間によって表示文字列を設定するような場合は次のように書くことができます。
<?php
define('SERVICE_1_WEEK', 1) ;
define('SERVICE_4_WEEKS', 2) ;
define('SERVICE_3_MONTHS', 4) ;
// $users に usersテーブルから全レコードを取得したものとする。
foreach ($users as $k => $v) {
if ($v['service_id'] & SERVICE_1_WEEK) {
$users['k']['description'] = '期間は1週間です';
} elseif ($v['service_id'] & SERVICE_4_WEEKS) {
$users['k']['description'] = '期間は4週間です';
} elseif ($v['service_id'] & SERVICE_3_MONTHS) {
$users['k']['description'] = '期間は3ヶ月です';
}
}
?>
まとめ
このようなビット演算の利用を考慮した設計が向いているのは、最初の設計段階でほぼ定義しきってしてしまえるような情報です。
レコードの追加削除や編集が多い情報では、ID列の値と他の列の値と不整合となったり、整合をとるのにコストがかかったりする可能性がありますので注意を。
算術演算だけでなく、ビット演算も利用することで、複雑になりやすいデータ群を整理してすっきりとしたプログラミングができますので、皆さんも取り入れてみてはいかがでしょうか。
