MariaDB/NoSQL Dynamic Columns の活用
以前、ここに hit.hateblo.jp 掲載して、結構満足していたのですが、今更になって、MariaDBにその機能が実装されていたという、なんともお恥ずかしい話しになります MariaDBにDynamic Columnsというものがありまして、これがまさしくRDBMSとNoSQLのハイブリッド構成ができるアレになります
いやいや、参った・・
Excelvan I5 Plus OLED Bluetooth4.0 スマートブレスレット 歩数計 着信番号表示 座りがち注意 IP65防水 日本語対応 (ブラック)
- 出版社/メーカー: Excelvan
- メディア: エレクトロニクス
- この商品を含むブログを見る
テーブル定義
以下のようなテーブルを作成しました。テーブル定義としては、普通のテーブルです、今回、商品マスタテーブルというものを作成してみました。この定義上にある、spec というblob型のカラムが、今回、Dynamic Columns で中心になる話しになります
DROP TABLE IF EXISTS products ; CREATE TABLE `products` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `created_user` int(11) NOT NULL DEFAULT '0', `updated_user` int(11) NOT NULL DEFAULT '0', `product_name` varchar(100) NOT NULL, `std_price` int(11) NOT NULL DEFAULT '0', `is_open` int(11) NOT NULL DEFAULT '0', `spec` blob, PRIMARY KEY (`id`), KEY `ix01_products` (`product_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品マスタ' ;
Dynamic Columns を設定する
上記テーブルに、以下のデータをINSERTします
特筆すべき点としては、COLUMN_CREATEというFUNCTIONを利用しているところです。これがいわゆる、Dynamic Columnsってやつです。動的にカラムをせっていできます。書式としては
COLUMN_CREATE(key, Value ....) ;
という形式でいれます。これは、連続で指定可能です。下で複数のkey/valueを設定しております、こんな感じで列挙できますね
SET @spec = COLUMN_CREATE('size', 'S', 'color','black', 'maker','しま○ら') ;
２件レコードをINSERTする
例として、属性が全く違う商品をインサートしてみました。１つは、服飾、そしてもう１つは、ロボットですねｗ なんでも売っているECサイトだった場合、同じテーブルに属性の違う商品を管理するっていうのが、良い例だと思いましたので、やってみました。
SET @product_name='涼やかデニム';
SET @is_open=0 ;
SET @std_price = 2900;
SET @spec = COLUMN_CREATE('size', 'S', 'color','black', 'maker','しま○ら') ;
INSERT INTO products
(
product_name,
std_price,
is_open,
spec
)
VALUES
(
@product_name,
@std_price,
@is_open,
@spec
) ;
SET @product_name='Peeepper（一般販売モデル）';
SET @is_open=0 ;
SET @std_price = 1200000;
SET @spec = COLUMN_CREATE('size', '1210×480×425 [mm]', 'weight','29kg', 'battery','リチウムイオンバッテリー') ;
INSERT INTO products
(
product_name,
std_price,
is_open,
spec
)
VALUES
(
@product_name,
@std_price,
@is_open,
@spec
) ;
２件とも無事に登録されているようです
MariaDB [item]> SELECT * FROM products ; +----+---------------------+---------------------+--------------+--------------+-------------------------------------+-----------+---------+--------------------------------------------------------------------------------------------------+ | id | created_at | updated_at | created_user | updated_user | product_name | std_price | is_open | spec | +----+---------------------+---------------------+--------------+--------------+-------------------------------------+-----------+---------+--------------------------------------------------------------------------------------------------+ | 1 | 2016-03-30 10:53:36 | 2016-03-30 10:53:36 | 0 | 0 | 涼やかデニム | 2900 | 0 | # izecolormaker!S!black!しま○ら | | 2 | 2016-03-30 10:54:21 | 2016-03-30 10:54:21 | 0 | 0 | Peeepper（一般販売モデル） | 1200000 | 0 | C izeweightbattery!1210×480×425 [mm]!29kg!リチウムイオンバッテリー | +----+---------------------+---------------------+--------------+--------------+-------------------------------------+-----------+---------+--------------------------------------------------------------------------------------------------+
Dynamic Columnsから特定の要素を抜き出す
先ほど登録した２件では、共通してsize というkeyで値を入れてみました。では、そのsizeというkeyには、どんなvalueが入っているのか確認してみたいと思います
COLUMN_GET
特定のカラムの値を取得したい場合、COLUMN_GETという関数を利用します。書式は
COLUMN_GET(dyncol_blob, column_name as type);
今回のデータでは、blobフィールドは、specになっているので、上記の書式に従い、SQLを書きました。
- こんな感じで値を取得できたかと思います
ちょっとめんどくさいですが、出力だけ見ると、通常のカラム取得となんら変わらないですよね？
SELECT id,product_name, COLUMN_GET(spec, 'size' as char) AS size FROM products; +----+-------------------------------------+---------------------+ | id | product_name | size | +----+-------------------------------------+---------------------+ | 1 | 涼やかデニム | S | | 2 | Peeepper（一般販売モデル） | 1210×480×425 [mm] | +----+-------------------------------------+---------------------+ -- 余談ですが、keyのないものを指定した場合、NULLがかえってきます SELECT id,product_name, COLUMN_GET(spec, 'color' as char) AS color FROM products; +----+-------------------------------------+-------+ | id | product_name | color | +----+-------------------------------------+-------+ | 1 | 涼やかデニム | black | | 2 | Peeepper（一般販売モデル） | NULL | <--- colorという要素はないのでNULLになります +----+-------------------------------------+-------+
その他の関数
Json で出力
プログラムで使う場合、おそらく大半が、jsonで扱ったほうが便利！だと思います。実は、そんな関数も揃ってます。以下に例を記載します
SELECT id,product_name,COLUMN_JSON(spec) FROM products ;
+----+-------------------------------------+-------------------------------------------------------------------------------------------------+
| id | product_name | COLUMN_JSON(spec) |
+----+-------------------------------------+-------------------------------------------------------------------------------------------------+
| 1 | 涼やかデニム | {"size":"S","color":"black","maker":"しま○ら"} |
| 2 | Peeepper（一般販売モデル） | {"size":"1210×480×425 [mm]","weight":"29kg","battery":"リチウムイオンバッテリー"} |
+----+-------------------------------------+-------------------------------------------------------------------------------------------------+
超COOL!!（だと・・
おまけ
カラム追加
Dynamic Columnsの利点として、同期にカラムを増やすことができます。通常のRDBMSですと、スキーマの変更すると、アプリケーションの影響を考慮しないといけないですよね？Dynamic Columnsは、物理的にカラムの増減がスキーマ上あるわけではないので楽ちんでCOOL! （だよね？・・・
COLUMN_ADD(dyncol_blob, column_nr, value [as type], [column_nr, value [as type]]...);
カラムを追加する場合は、COLUMN_ADDを利用します
- 書式
COLUMN_ADD(dyncol_blob, column_name, value [as type], [column_name, value [as type]]...);
COLUMN_ADD
機械系のspecに、platformとdisplayの要素を追加する例で説明します
こんな感じでSQLを書きます
UPDATE products SET spec=COLUMN_ADD(spec, 'platform', 'NAOqi OS' AS CHAR,'display','10' AS INT ) WHERE id=2;
通常のカラム追加ですと、ALTER TABLE ADD COLUMN YYYY とかなのですが、Dynamic columnの場合は、SQLのUPDATE(COLUMN_ADD)構文で追加したりします。
COLUMN_EXISTS
対象のカラムが存在するかチェックします
- COLUMN_EXISTS
-- sizeという名前のカラムが存在するか？ SELECT COLUMN_EXISTS(spec, 'size') FROM products ; +-----------------------------+ | COLUMN_EXISTS(spec, 'size') | +-----------------------------+ | 1 | | 1 | +-----------------------------+ -- platformという名前のカラムが存在するか？ SELECT COLUMN_EXISTS(spec, 'platform') FROM products ; +---------------------------------+ | COLUMN_EXISTS(spec, 'platform') | +---------------------------------+ | 0 | | 1 | +---------------------------------+ 存在する場合は、1、存在しない場合は、0になります
- COLUMN_LIST
カラムのリストを取得する
SELECT id,product_name,COLUMN_LIST(spec) FROM products ; +----+-------------------------------------+------------------------------------------------+ | id | product_name | COLUMN_LIST(spec) | +----+-------------------------------------+------------------------------------------------+ | 1 | 涼やかデニム | `size`,`color`,`maker` | | 2 | Peeepper（一般販売モデル） | `size`,`weight`,`battery`,`display`,`platform` | +----+-------------------------------------+------------------------------------------------+
- COLUMN_DELETE
読んで字のごとくカラムの削除をします（あんまし使わなさそうだけど
-- id=1の、makerを削除する UPDATE products SET spec=COLUMN_DELETE(spec, 'maker') WHERE id = 1 ; --- Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 /* カラムが消えているかチェック */ SELECT id,product_name,COLUMN_LIST(spec) FROM products ; +----+-------------------------------------+------------------------------------------------+ | id | product_name | COLUMN_LIST(spec) | +----+-------------------------------------+------------------------------------------------+ | 1 | 涼やかデニム | `size`,`color` | | 2 | Peeepper（一般販売モデル） | `size`,`weight`,`battery`,`display`,`platform` | +----+-------------------------------------+------------------------------------------------+
如何でしょうか？なんとなくスキーマレスとのハイブリッド構成が利用できる利点がご理解いただけたのではないかと思います。
以上、だいたいこのあたりが基本的な使い方になってくるかなーって思ってます。もっと高度な利用方法としては、 カラムのネスティングにも使えたりします。詳しい話しにつきましては
https://mariadb.com/kb/en/mariadb/dynamic-columns/
を参考にどうぞ
