DBでバイナリ型にSELECT文実行 #MySQL

むかしむかしあるところにおじいさんとおばあさんが住んでました。
おじいさんは山へ芝刈りに。
おばあさんはDBのデータを少しでも軽くしようと文字列をバイナリ型にして保存させましたとさ。
そしておばあさんはいいました。
　「バイナリ型だけどSQLでとれんの、これ？」

ということでやってみました。
※一旦AWSはお休み。

準備

バイナリ型と文字列型で同じデータを入れます。

mysql> create table test2(str1 binary(16), str2 char(32));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2;
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `str1` binary(16) DEFAULT NULL,
  `str2` char(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> insert into test2(str1, str2) values (unhex('f1234567890abcdef1234567890abcde'), 'f1234567890abcdef1234567890abcde');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2(str1, str2) values (unhex('1234567890abcdef1234567890abcdef'), '1234567890abcdef1234567890abcdef');
Query OK, 1 row affected (0.00 sec)

入れたデータはこんな感じ

mysql> select str1,  str2 from test2;
+------------------+----------------------------------+
| str1             | str2                             |
+------------------+----------------------------------+
| �#Eg�
���#Eg�
��         | f1234567890abcdef1234567890abcde |
| 4Vx����4Vx����         | 1234567890abcdef1234567890abcdef |
+------------------+----------------------------------+
2 rows in set (0.00 sec)


mysql> select hex(str1),  str2 from test2;
+----------------------------------+----------------------------------+
| hex(str1)                        | str2                             |
+----------------------------------+----------------------------------+
| F1234567890ABCDEF1234567890ABCDE | f1234567890abcdef1234567890abcde |
| 1234567890ABCDEF1234567890ABCDEF | 1234567890abcdef1234567890abcdef |
+----------------------------------+----------------------------------+
2 rows in set (0.00 sec)

検証開始

まず普通にselectしてみる。

mysql> select str1,  str2 from test2 where str1="1234567890ABCDEF1234567890ABCDEF";
Empty set (0.00 sec)

mysql> select hex(str1),  str2 from test2 where str1="1234567890ABCDEF1234567890ABCDEF";
Empty set (0.00 sec)

やっぱり取れないですね。
当然str2のほうならとれます。

mysql> select str1,  str2 from test2 where str2="1234567890ABCDEF1234567890ABCDEF";
+------------------+----------------------------------+
| str1             | str2                             |
+------------------+----------------------------------+
| 4Vx����4Vx����         | 1234567890abcdef1234567890abcdef |
+------------------+----------------------------------+
1 row in set (0.00 sec)

ではどうするか。。
私が思いつく方法は二つあります。
①別名をつけてHavingで検索
②where内でバイナリ化させた文字列で検索

①別名をつけてHavingで検索

mysql> select hex(str1) as str1_ex,  str2 from test2 having str1_ex="1234567890ABCDEF1234567890ABCDEF";
+----------------------------------+----------------------------------+
| str1_ex                          | str2                             |
+----------------------------------+----------------------------------+
| 1234567890ABCDEF1234567890ABCDEF | 1234567890abcdef1234567890abcdef |
+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

②where内でバイナリ化させた文字列で検索

mysql> select hex(str1),  str2 from test2 where str1=unhex("1234567890ABCDEF1234567890ABCDEf");
+----------------------------------+----------------------------------+
| hex(str1)                        | str2                             |
+----------------------------------+----------------------------------+
| 1234567890ABCDEF1234567890ABCDEF | 1234567890abcdef1234567890abcdef |
+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

両方取れましたね。
ただ①ですと問題があります。
Havingを使用するとindexが効かないのです。

少しずれますがインデックスのお話。

mysql> create index test_idx on test2(str1); 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index test_idx2 on test2(str2); 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `str1` binary(16) DEFAULT NULL,
  `str2` char(32) DEFAULT NULL,
  KEY `test_idx` (`str1`),
  KEY `test_idx2` (`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ちょっとテーブルにインデックスを張って
今まで実行したselectの実行計画を確認すると・・・

mysql> explain select hex(str1) as str1_ex,  str2 from test2 having str1_ex="1234567890ABCDEF1234567890ABCDEf";
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

「type：ALL」・・・使われてないですね。
でも②なら！

mysql> explain select hex(str1),  str2 from test2 where str1=unhex("1234567890ABCDEF1234567890ABCDEf");
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test2 | ref  | test_idx      | test_idx | 17      | const |    1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

完璧！

ちゃんと取れたよおばあさん。
めでたしめでたし。

・
・
・
とはいかなかったのが正直想定外でした。
ためしにwhereのなかでin句を使用してみると

mysql> explain select hex(str1),  str2 from test2 where str1 in (unhex("1234567890ABCDEF1234567890ABCDEf"),unhex("f1234567890abcdef1234567890abcde"));
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | ALL  | test_idx      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

また出たな「type：ALL」よ。

これ調べたけど正直よくわからなかったです。。。
inの中でunhexしてるからな気がしますが、裏付けまでは取れなかったです。

一応回避法

force indexを使うのが一番手っ取り早そうです。
※use indexではだめでした。

mysql> explain select hex(str1),  str2 from test2 force index (test_idx) where str1 in (unhex("1234567890ABCDEF1234567890ABCDEf"),unhex("f1234567890abcdef1234567890abcde"));
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | range | test_idx      | test_idx | 17      | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select hex(str1),  str2 from test2 use index (test_idx) where str1 in (unhex("1234567890ABCDEF1234567890ABCDEf"),unhex("f1234567890abcdef1234567890abcde"));
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | ALL  | test_idx      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

おしまい。