MySQLで16進数のASCIIデータを取り扱う
経緯
SNMPでデータ取得をしてMySQLにぶち込むスクリプトを組んでいたところ、HEX型で返ってくるデータがあったのでとりあえずそのままぶち込んだ。
そのあと、データを取り出してASCII文字列に変換するのにちょっと困った話。
やったこと
mysql> CREATE TABLE test (hexdata varchar(512) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.21 sec)
mysql> insert into test values '0x544553547465737421'; #TESTtest!
Query OK, 1 row affected (0.00 sec)
mysql> SELECT hexdata FROM test;
+----------------------+
| hexdata |
+----------------------+
| 0x544553547465737421 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT unhex(hexdata) FROM test;
+----------------+
| unhex(hexdata) |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
->unhex効かない？
mysql> SELECT unhex(substring(hexdata,3,length(hexdata))) FROM test;
+---------------------------------------------+
| unhex(substring(hexdata,3,length(hexdata))) |
+---------------------------------------------+
| TESTtest! |
+---------------------------------------------+
1 row in set (0.00 sec)
->unhexは0-9A-Fで書かれたデータしか変換できないので、頭に'0x'があった場合は削除する必要ある。
以上。
ダメだったこと
16進法をASCII化するには
SELECT x'TESTtest!'
みたいに書いてあることがあるんだけど、x構文ってテーブルデータに対して使えない気がする。
mysql> SELECT x'0x544553547465737421' ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'0x544553547465737421'' at line 1
mysql> SELECT x'544553547465737421' ;
+-----------------------+
| x'544553547465737421' |
+-----------------------+
| TESTtest! |
+-----------------------+
1 row in set (0.00 sec)
/* x構文でも頭の'0x'は外す必要ある。
じゃあ、テーブルのレコードに対してx構文ってどうやって使う？ */
mysql> SELECT x hexdata FROM test;
ERROR 1054 (42S22): Unknown column 'x' in 'field list'
/* 行と解釈されてError.当たり前 */
mysql> SELECT x(substring(hexdata,3,length(hexdata))) FROM test;
+-----------------------------------------+
| x(substring(hexdata,3,length(hexdata))) |
+-----------------------------------------+
| NULL |
+-----------------------------------------+
1 row in set (0.00 sec)
/* 出てこない。 */
じゃあ、HEXデータでフィルタは？
HEXデータそのままでは使いづらいため、やっぱり文字列変換後にフィルタを書きたい。
HEXデータの場合、ある程度の制御コードも入ってくることが想定される(ていうか、実際に混ざってきてた)。
なので、まずはNULL(0x00)が入っていることを想定。
mysql> TRUNCATE test;
Query OK, 0 rows affected (0.34 sec)
mysql> insert into test values ('0x544553547465737421'),('0x00544553547465737421'),('0x544553547465737421005454'),('0x00544553547465737421005353');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT hexdata,unhex(substring(hexdata,3,length(hexdata))) hex2ascii FROM test ;
+------------------------------+---------------+
| hexdata | hex2ascii |
+------------------------------+---------------+
| 0x544553547465737421 | TESTtest! |
| 0x00544553547465737421 | TESTtest! |
| 0x544553547465737421005454 | TESTtest! TT |
| 0x00544553547465737421005353 | TESTtest! SS |
+------------------------------+---------------+
4 rows in set (0.00 sec)
/* まずはLikeで抽出 */
mysql> SELECT hexdata,unhex(substring(hexdata,3,length(hexdata))) hex2ascii FROM test WHERE unhex(substring(hexdata,3,length(hexdata))) like 'TEST%';
+----------------------------+--------------+
| hexdata | hex2ascii |
+----------------------------+--------------+
| 0x544553547465737421 | TESTtest! |
| 0x544553547465737421005454 | TESTtest! TT |
+----------------------------+--------------+
2 rows in set (0.00 sec)
->頭にNULL文字が入っている行は抽出できない。一応データ扱いらしい。
mysql> SELECT hexdata,unhex(substring(hexdata,3,length(hexdata))) hex2ascii FROM test WHERE unhex(substring(hexdata,3,length(hexdata))) like '%TEST%';
+------------------------------+---------------+
| hexdata | hex2ascii |
+------------------------------+---------------+
| 0x544553547465737421 | TESTtest! |
| 0x00544553547465737421 | TESTtest! |
| 0x544553547465737421005454 | TESTtest! TT |
| 0x00544553547465737421005353 | TESTtest! SS |
+------------------------------+---------------+
4 rows in set (0.00 sec)
->出てきた。
/* LikeはわかったのでRegexpで抽出。 */
mysql> SELECT hexdata,unhex(substring(hexdata,3,length(hexdata))) hex2ascii FROM test WHERE unhex(substring(hexdata,3,length(hexdata))) regexp 'TEST';
+----------------------------+--------------+
| hexdata | hex2ascii |
+----------------------------+--------------+
| 0x544553547465737421 | TESTtest! |
| 0x544553547465737421005454 | TESTtest! TT |
+----------------------------+--------------+
2 rows in set (0.00 sec)
->？？？？
mysql> SELECT hexdata,unhex(substring(hexdata,3,length(hexdata))) hex2ascii FROM test WHERE unhex(substring(hexdata,3,length(hexdata))) regexp 'TT';
Empty set (0.00 sec)
->？？？？？！！！！
unhexしたデータでフィルタかける場合、LIKEはある程度想定通りの動作をするが、REGEXPはnulll(0x00)が入っているとその後ろのデータは抽出条件として使えないっぽい。