MariaDB 10.0.12でDynamic Columns（動的カラム）を試してみた

新サイトへ移転しました
約３秒後に自動的にリダイレクトします。

MariaDBではDynamic Columnsなるものが実装されている。version 5.3から新たに導入されたもののようだ。

Dynamic Columns - MariaDB Knowledge Baseより

MariaDB starting with 5.3
Dynamic columns first appeared in MariaDB-5.3

~~「MariaDB Dynamic Columns」でぐぐってみたけど、日本語のページがほとんどHITしなかったのでちょっとかいてみる。（実は既にあったりして。。）~~>>（追記：もうちょっとぐぐったら、すぐ見つかった。結構たくさん。お恥ずかしい。。

今回の記事は、実際にMariaDBでDynamic Columnsを使ってみた過程をそのまま載せています。後日またまとめる予定ですが、長いのであしからず。
最後の方に、動的カラムからJSON形式でデータを得る方法（といっても超簡単）を載せています。そちらが目当ての方は最下部へどうぞ。

◯テーブル作成

「blob」と指定することで、カラムの追加・削除などが動的に行えるようになる。

MariaDB [test_db]> create table assets(
    -> item_name varchar(32) primary key,
    -> dynamic_cols blob
    -> );
Query OK, 0 rows affected (0.20 sec)

ちなみに、blobってどういう意味だろうと思ってALCさんに尋ねたら「輪郭がぼやけているもの、おぼろげなもの、フニャフニャした物体［生き物］」「〈俗〉でぶ、丸ぽちゃ、ふとっちょ」といった意味があるようだ（ALCで調べました）。

さっそくフィールドの制約などを確認してみる。

MariaDB [test_db]> show fields from assets;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| item_name    | varchar(32) | NO   | PRI | NULL    |       |
| dynamic_cols | blob        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

おー、Typeがblobになってる（そりゃそうだ。）

◯テーブルに追加

動的カラムにデータをつかするには以下のようにする。以下の例は、２つの動的カラム（color=blue, size=XL）をもつデータを追加するときの例。

MariaDB [test_db]> insert into assets values
    -> ('MariaDB Tshirt', column_create('color', 'blue', 'size', 'XL')
    -> );
Query OK, 1 row affected (0.04 sec)

この段階でテーブルの中身を確認してみる。

+----------------+--------------------------------+
| item_name      | dynamic_cols                   |
+----------------+--------------------------------+
| MariaDB Tshirt |           3 sizecolor!XL!blue |
+----------------+--------------------------------+
1 row in set (0.00 sec)

なんか、よくわからん。が、2でもなく4でもなく、「3」という数字が。どういうことだ。まぁ今は置いておく。
で、上と同様にして、今度はcolor=black, price=500という動的カラムをもつデータを追加する（この表現は正しいのか？）。

MariaDB [test_db]> insert into assets values
    -> ('Thinkpad Laptop', column_create('color', 'black', 'price', 500));
Query OK, 1 row affected (0.03 sec)

書き方が変わってるのはご愛嬌。同様に中身を確認してみる。

MariaDB [test_db]> select * from assets;
+-----------------+---------------------------------+
| item_name       | dynamic_cols                    |
+-----------------+---------------------------------+
| MariaDB Tshirt  |          3 sizecolor!XL!blue  |
| Thinkpad Laptop |
     ` colorprice!black・ |
+-----------------+---------------------------------+
2 rows in set (0.00 sec)

表示が崩れていますが、これは、このブログで表示が崩れているのではなく、本当にこのように崩れて表示されました。なぜだ。今回は「3 pricesize!500!black」と表示されると予想してたのに。

公式ページの説明を読み進めていくと、以下のようにすると決行いい感じの情報が得られることがわかった。

MariaDB [test_db]> select item_name, column_list(dynamic_cols) from assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB Tshirt  | `size`,`color`            |
| Thinkpad Laptop | `color`,`price`           |
+-----------------+---------------------------+
2 rows in set (0.00 sec)

◯動的カラムからデータを取り出す

上のままでは、動的カラムからいい感じの情報を取り出せない。ちゃんと取り出すには以下のようにする。

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'color' as char) from assets;
+-----------------+-------------------------------------------+
| item_name       | column_get(dynamic_cols, 'color' as char) |
+-----------------+-------------------------------------------+
| MariaDB Tshirt  | blue                                      |
| Thinkpad Laptop | black                                     |
+-----------------+-------------------------------------------+
2 rows in set (0.00 sec)

同様にして、色々と試してみる。

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'size' as char) from assets;
+-----------------+------------------------------------------+
| item_name       | column_get(dynamic_cols, 'size' as char) |
+-----------------+------------------------------------------+
| MariaDB Tshirt  | XL                                       |
| Thinkpad Laptop | NULL                                     |
+-----------------+------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'price' as char) from assets;
+-----------------+-------------------------------------------+
| item_name       | column_get(dynamic_cols, 'price' as char) |
+-----------------+-------------------------------------------+
| MariaDB Tshirt  | NULL                                      |
| Thinkpad Laptop | 500                                       |
+-----------------+-------------------------------------------+
2 rows in set (0.00 sec)

Thinkpad Laptopのsizeだったり、MariaDB Tshirtのpriceのように、設定されていないものはNULLとなるらしい。どうせなら、設定されていないものは表示されないで欲しかったかな。以下の実行結果から想像したことだが、「'color' as char」や「'size' as char」の部分は、データを取り出すときにchar型やint型に変換してくれてるっぽい。

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'price' as int) from assets;
+-----------------+------------------------------------------+
| item_name       | column_get(dynamic_cols, 'price' as int) |
+-----------------+------------------------------------------+
| MariaDB Tshirt  |                                     NULL |
| Thinkpad Laptop |                                      500 |
+-----------------+------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'price' as char) from assets;
+-----------------+-------------------------------------------+
| item_name       | column_get(dynamic_cols, 'price' as char) |
+-----------------+-------------------------------------------+
| MariaDB Tshirt  | NULL                                      |
| Thinkpad Laptop | 500                                       |
+-----------------+-------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'price' as double) from assets;
+-----------------+---------------------------------------------+
| item_name       | column_get(dynamic_cols, 'price' as double) |
+-----------------+---------------------------------------------+
| MariaDB Tshirt  |                                        NULL |
| Thinkpad Laptop |                                         500 |
+-----------------+---------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'size' as double) from assets;
+-----------------+--------------------------------------------+
| item_name       | column_get(dynamic_cols, 'size' as double) |
+-----------------+--------------------------------------------+
| MariaDB Tshirt  |                                          0 |
| Thinkpad Laptop |                                       NULL |
+-----------------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'size' as int) from assets;
+-----------------+-----------------------------------------+
| item_name       | column_get(dynamic_cols, 'size' as int) |
+-----------------+-----------------------------------------+
| MariaDB Tshirt  |                                       0 |
| Thinkpad Laptop |                                    NULL |
+-----------------+-----------------------------------------+
2 rows in set, 1 warning (0.00 sec)

なるほど、文字で格納されているものを数字に変換しようとなると0、ただし、値が入っていないものはNULLと。まぁ当然といえば当然か。

ちなみに、型変換を行う部分を省略するとエラーとなった。かならず、型を指定して取り出さないといけない様子。

MariaDB [test_db]> select item_name, column_get(dynamic_cols, 'color') from assets;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') from assets' at line 1

◯カラムの追加・削除

動的カラムは追加・削除が可能らしい。"color"=blackであるデータの"price"情報を削除する場合は以下のようにする。

MariaDB [test_db]> update assets set dynamic_cols=column_delete(dynamic_cols, "price") where column_get(dynamic_col
s, 'color' as char)='black';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test_db]> select item_name, column_list(dynamic_cols) from assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB Tshirt  | `size`,`color`            |
| Thinkpad Laptop | `color`                   |
+-----------------+---------------------------+
2 rows in set (0.00 sec)

どのようになったか確認もしてみたが、Thinkpad Laptopから'price'の情報がなくなっている。

逆に、新たにカラムを追加する場合は以下のようにする。

MariaDB [test_db]> update assets set dynamic_cols=column_add(dynamic_cols, "warranty", "3 years") where item_name='
Thinkpad Laptop';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test_db]> select item_name, column_list(dynamic_cols) from assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB Tshirt  | `size`,`color`            |
| Thinkpad Laptop | `color`,`warranty`        |
+-----------------+---------------------------+
2 rows in set (0.00 sec)

◯動的カラムの中身をJSONで取り出す。

Mariadb 10.0.1からは、動的カラムの中身をJSONで取り出す、ということも可能なようだ。なんてことだ。便利すぎる。

MariaDB [test_db]> select item_name, column_json(dynamic_cols) from assets;
+-----------------+----------------------------------------+
| item_name       | column_json(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB Tshirt  | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+
2 rows in set (0.00 sec)