HTMLの色コードをDBに文字列化して保存する場合はあると思う。この値を一様に加工して別な色値を求めたい時など、16進数文字列の計算をDB側で済ませたい。
# （例）明るいトーンにしたい
もとの文字列: 1234ab
やりたいこと: (0x1234ab | 0xc0c0c0) -> #D2F4eb
例えば、こんなサンプルデータの場合、
+-----+--------+
| id | color |
+-----+--------+
| 100 | 1234ab |
+-----+--------+
次のように、CONV()を用いて10進数化して計算が可能である。
mysql> select color, hex(conv(color,16,10) | 0xc0c0c0) from colors;
+--------+-----------------------------------+
| color | hex(conv(color,16,10) | 0xc0c0c0) |
+--------+-----------------------------------+
| 1234ab | D2F4EB |
+--------+-----------------------------------+
以下、メモ。(MySQL v5.6.19で検証）
MySQLの16進値の基本
リテラル
幾つか書き方があるが、一般的な言語にある0x1234abのような書き方で、数値と同様に計算できる。
mysql> select 0x1234ab+1;
+------------+
| 0x1234ab+1 |
+------------+
| 1193132 |
+------------+
また、自動的にキリの良い桁（偶数桁）になるよう内部で0パディングしている。
mysql> select hex(0xa),hex(0xabc);
+----------+------------+
| hex(0xa) | hex(0xabc) |
+----------+------------+
| 0A | 0ABC |
+----------+------------+
デフォルトでは16進値のデータ型は文字列とみなされるので、数値として計算したい場合はダミーなどの計算式を入れ、数値型にキャストする。
mysql> select 0x40, 0x40+0;
+------+--------+
| 0x40 | 0x40+0 |
+------+--------+
| @ | 64 |
+------+--------+
HEX()関数、UNHEX()関数
数値を16進数文字列に変換したい場合は、HEX()関数に数値を与えて行う。
mysql> select hex(0x123456+0);
+-----------------+
| hex(0x123456+0) |
+-----------------+
| 123456 |
+-----------------+
ちなみに、HEX()関数の引数に文字列を与えると、そのバイナリコードを返す。数値の場合と動作が異なるので注意。
mysql> select hex(0xabc), hex("0xabc"), hex("abc"), hex("あア愛");
+------------+--------------+------------+--------------------+
| hex(0xabc) | hex("0xabc") | hex("abc") | hex("あア愛") |
+------------+--------------+------------+--------------------+
| 0ABC | 3078616263 | 616263 | E38182E382A2E6849B |
+------------+--------------+------------+--------------------+
また、UNHEX()という関数があり、これは、このHEX(文字列)の逆のことをする関数になる。
mysql> select hex(unhex("abc")),unhex("616263"),unhex("E38182E382A2E6849B");
+-------------------+-----------------+-----------------------------+
| hex(unhex("abc")) | unhex("616263") | unhex("E38182E382A2E6849B") |
+-------------------+-----------------+-----------------------------+
| 0ABC | abc | あア愛 |
+-------------------+-----------------+-----------------------------+
UNHEX("abc")では、[0x61, 0x62, 0x63] というバイナリの16進数文字列が作られるが、これは３桁なので頭にパディングの0x00が入り、[0x00, 0x61, 0x62, 0x63] となる。上の例 hex(unhex("abc")) では、それゆえ、0ABC という結果が返されている。
数値文字列のキャスト
MySQLは、10進数文字列と数値の演算は、文字列をキャストして計算する。
しかし、16進数文字列の場合は数値に変換せず、計算もしない。この場合CONV()関数を使う。
暗黙的なキャスト
通常の10進数などの数値表現の文字列であれば、暗黙的にキャストして計算してくれる。
mysql> select "18782"+18782,"1.23"+0.004;
+---------------+--------------+
| "18782"+18782 | "1.23"+0.004 |
+---------------+--------------+
| 37564 | 1.234 |
+---------------+--------------+
しかし、16進数の文字列はキャストしてくれない。
mysql> select "0x123"+1, 2+"0x456";
+-----------+-----------+
| "0x123"+1 | 2+"0x456" |
+-----------+-----------+
| 1 | 2 |
+-----------+-----------+
CAST()関数とCONVERT()関数
CAST()関数とCONVERT()関数は、数値を表す文字列を明示的に任意の型にキャストしてくれる。
mysql> select cast("-123" as signed)+1;
+--------------------------+
| cast("-123" as signed)+1 |
+--------------------------+
| -122 |
+--------------------------+
しかし、16進数は認識してくれないようだ。
mysql> select cast("0x12" as signed)+1;
+--------------------------+
| cast("0x12" as signed)+1 |
+--------------------------+
| 1 |
+--------------------------+
16進数文字列の数値化
CONV()関数
CONV()関数を使う。これは進数変換をする関数だ（基数の変換）。0xを除いた16進数文字列を指し示して10進数に変換が可能である（なお、返されるのは文字列である）。
mysql> select 0x1234ab+0, conv("1234ab",16,10);
+------------+----------------------+
| 0x1234ab+0 | conv("1234ab",16,10) |
+------------+----------------------+
| 1193131 | 1193131 |
+------------+----------------------+
なので、一旦CONV()で10進数表現にしてから演算をすると、最初の目的が果たせる。
mysql> select color, hex(conv(color,16,10) | 0xc0c0c0) from colors;
+--------+-----------------------------------------------+
| color | hex(conv(hex(unhex(color)),16,10) | 0xc0c0c0) |
+--------+-----------------------------------------------+
| 1234ab | D2F4EB |
+--------+-----------------------------------------------+
なお、CONV() は、10進数から16進数の変換を指し示して、HEX()と同じことをさせるのが可能。
mysql> select hex(4096),conv(4096,10,16);
+-----------+------------------+
| hex(4096) | conv(4096,10,16) |
+-----------+------------------+
| 1000 | 1000 |
+-----------+------------------+
まとめ
HEX()とCONV()の整理
さくっとまとめよう。
- 10進数値→16進数文字列の変換：
HEX(n)またはCONV(n,10,16)
mysql> select hex(4096),conv(4096,10,16);
+-----------+------------------+
| hex(4096) | conv(4096,10,16) |
+-----------+------------------+
| 1000 | 1000 |
+-----------+------------------+
- 16進数文字列→10進数値文字列の変換：
CONV(x,16,10)
mysql> select 0x1234ab+0, conv("1234ab",16,10)+0;
+------------+------------------------+
| 0x1234ab+0 | conv("1234ab",16,10)+0 |
+------------+------------------------+
| 1193131 | 1193131 |
+------------+------------------------+
（以上）