MySQLの16進数の整理と16進数文字列の演算

HTMLの色コードをDBに文字列化して保存する場合はあると思う。この値を一様に加工して別な色値を求めたい時など、16進数文字列の計算をDB側で済ませたい。

```
# （例）明るいトーンにしたい
もとの文字列: 1234ab
やりたいこと: (0x1234ab | 0xc0c0c0) -> #D2F4eb
```

例えば、こんなサンプルデータの場合、

```
+-----+--------+
| id  | color  |
+-----+--------+
| 100 | 1234ab |
+-----+--------+
```

次のように、CONV()を用いて10進数化して計算が可能である。

```mysql
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進値の基本

### リテラル

[幾つか書き方があるが](https://dev.mysql.com/doc/refman/5.6/ja/hexadecimal-literals.html)、一般的な言語にある`0x1234ab`のような書き方で、数値と同様に計算できる。

```mysql
mysql> select 0x1234ab+1;
+------------+
| 0x1234ab+1 |
+------------+
|    1193132 |
+------------+
```

また、自動的にキリの良い桁（偶数桁）になるよう内部で0パディングしている。

```mysql
mysql> select hex(0xa),hex(0xabc);
+----------+------------+
| hex(0xa) | hex(0xabc) |
+----------+------------+
| 0A       | 0ABC       |
+----------+------------+
```

デフォルトでは16進値のデータ型は文字列とみなされるので、数値として計算したい場合はダミーなどの計算式を入れ、数値型にキャストする。

```mysql
mysql> select 0x40, 0x40+0;
+------+--------+
| 0x40 | 0x40+0 |
+------+--------+
| @    |     64 |
+------+--------+
```

### HEX()関数、UNHEX()関数

数値を16進数文字列に変換したい場合は、[`HEX()`関数](https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_hex)に**数値**を与えて行う。

```mysql
mysql> select hex(0x123456+0);
+-----------------+
| hex(0x123456+0) |
+-----------------+
| 123456          |
+-----------------+
```

ちなみに、`HEX()`関数の引数に文字列を与えると、そのバイナリコードを返す。数値の場合と動作が異なるので注意。

```mysql
mysql> select hex(0xabc), hex("0xabc"), hex("abc"), hex("あア愛");
+------------+--------------+------------+--------------------+
| hex(0xabc) | hex("0xabc") | hex("abc") | hex("あア愛")      |
+------------+--------------+------------+--------------------+
| 0ABC       | 3078616263   | 616263     | E38182E382A2E6849B |
+------------+--------------+------------+--------------------+
```

また、[`UNHEX()`という関数があり](https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_unhex)、これは、この`HEX(文字列)`の逆のことをする関数になる。

```mysql
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
mysql> select "18782"+18782,"1.23"+0.004;
+---------------+--------------+
| "18782"+18782 | "1.23"+0.004 |
+---------------+--------------+
|         37564 |        1.234 |
+---------------+--------------+
```

しかし、16進数の文字列はキャストしてくれない。

```mysql
mysql> select "0x123"+1, 2+"0x456";
+-----------+-----------+
| "0x123"+1 | 2+"0x456" |
+-----------+-----------+
|         1 |         2 |
+-----------+-----------+
```

### CAST()関数とCONVERT()関数

[`CAST()`関数と`CONVERT()`関数](https://dev.mysql.com/doc/refman/5.6/ja/cast-functions.html#function_cast)は、数値を表す文字列を明示的に任意の型にキャストしてくれる。

```mysql
mysql> select cast("-123" as signed)+1;
+--------------------------+
| cast("-123" as signed)+1 |
+--------------------------+
|                     -122 |
+--------------------------+
```

しかし、16進数は認識してくれないようだ。

```mysql
mysql> select cast("0x12" as signed)+1;
+--------------------------+
| cast("0x12" as signed)+1 |
+--------------------------+
|                        1 |
+--------------------------+
```

## 16進数文字列の数値化

### CONV()関数

`CONV()`関数を使う。これは進数変換をする関数だ（基数の変換）。`0x`を除いた16進数文字列を指し示して10進数に変換が可能である（なお、返されるのは文字列である）。

```mysql
mysql> select 0x1234ab+0, conv("1234ab",16,10);
+------------+----------------------+
| 0x1234ab+0 | conv("1234ab",16,10) |
+------------+----------------------+
|    1193131 | 1193131              |
+------------+----------------------+
```

なので、一旦`CONV()`で10進数表現にしてから演算をすると、最初の目的が果たせる。

```mysql
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
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
mysql> select hex(4096),conv(4096,10,16);
+-----------+------------------+
| hex(4096) | conv(4096,10,16) |
+-----------+------------------+
| 1000      | 1000             |
+-----------+------------------+
```

- 16進数文字列→10進数値文字列の変換：`CONV(x,16,10)`

```mysql
mysql> select 0x1234ab+0, conv("1234ab",16,10)+0;
+------------+------------------------+
| 0x1234ab+0 | conv("1234ab",16,10)+0 |
+------------+------------------------+
|    1193131 |                1193131 |
+------------+------------------------+
```

（以上）