(2014.12.3追記：以下の書籍にも記述した。)
(2014.4.16)微妙に反応があるので、PostgreSQLネタ追記。
PostgreSQLには古くから配列型や複合型というのがあって、似たようなことができる。
あと、MariaDBのダイナミックカラムをJSONで取り出すcolumn_jsonなんてのもあるが、PostgreSQLはネイティブなデータ型としてJSON型があるので、わざわざBLOBを使わずとも、最初からJSON型でデータ処理できる。
そもそも、こんな感じで自由にデータ型も追加できたりと、中の作りはPostgreSQLのほうが圧倒的に出来が良い。
以下、本文。
MariaDB 5.3で導入されたダイナミックカラムという機能が、MariaDB 10.0でかなり使いやすくなった*1。
これは、BLOB型のカラムの内部を独自フォーマットで分割し、一つのカラムに複数のデータを格納する機能である。分割する個数は後から自由に変更できるのでダイナミック(動的)と名付けられている。
具体例をみるのが最も分かりやすいので、以下に簡単な例を示す。
テーブルdynamic_col_testを作成して、データをインサートする。ダイナミックカラムとして使うカラムにはBLOB型を指定。
データをインサートする際、ダイナミックカラム型のカラムには関数COLUMN_CREATE()を使うところがこの機能のキーポイント。この関数によってBLOB型内部を独自フォーマットで分割してデータを格納する。
MariaDB [sampledb]> CREATE TABLE dynamic_col_test (id int, dynamic_col blob);
Query OK, 0 rows affected (1.48 sec)
MariaDB [sampledb]> INSERT INTO dynamic_col_test
-> VALUES (1, COLUMN_CREATE('name', 'Alice', 'color', 'red'));
Query OK, 1 row affected (0.54 sec)
MariaDB [sampledb]> INSERT INTO dynamic_col_test
-> VALUES (1, COLUMN_CREATE('name', 'Bob', 'color', 'blue'));
Query OK, 1 row affected (0.23 sec)
ダイナミックカラムの内部データを取り出す場合には関数COLUMN_GET()を使う。
MariaDB [sampledb]> SELECT id, COLUMN_GET(dynamic_col, 'name' as char) as name
-> FROM dynamic_col_test;
+
| id | name |
+
| 1 | Alice |
| 2 | Bob |
+
2 rows in set (0.00 sec)
ダイナミックカラムの項目を追加するには、次のようにする。
MariaDB [sampledb]> UPDATE dynamic_col_test SET dynamic_col = COLUMN_ADD(dynamic_col, 'age', 27)
-> WHERE id = 1;
Query OK, 1 row affected (0.44 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [sampledb]> UPDATE dynamic_col_test SET dynamic_col = COLUMN_ADD(dynamic_col, 'age', 26)
-> WHERE id = 2;
Query OK, 1 row affected (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [sampledb]> SELECT id, COLUMN_GET(dynamic_col, 'age' as int) as age
-> FROM dynamic_col_test;
+
| id | age |
+
| 1 | 27 |
| 2 | 26 |
+
2 rows in set (0.07 sec)
このようにダイナミックカラムは、COLUMN_XXXという関数を介してBLOB型で格納したデータを操作する。この仕組みから明らかなように、ダイナミックカラムで格納した項目についてインデックスを張ることはできない。
使い勝手のよいキーバリューストアとして使うとよいかも。普通のテーブルとJOINできる訳だし。
詳細はこちらを参照。チュートリアル風に使い方を説明している。
