読者です 読者をやめる 読者になる 読者になる

Database JUNKY

MySQL,MariaDBを中心としたブログです

MariaDB/NoSQL Dynamic Columns の活用

SQL NoSQL KVS MariaDB

以前、ここに hit.hateblo.jp 掲載して、結構満足していたのですが、今更になって、MariaDBにその機能が実装されていたという、なんともお恥ずかしい話しになります MariaDBにDynamic Columnsというものがありまして、これがまさしくRDBMSとNoSQLのハイブリッド構成ができるアレになります

いやいや、参った・・

テーブル定義

以下のようなテーブルを作成しました。テーブル定義としては、普通のテーブルです、今回、商品マスタテーブルというものを作成してみました。この定義上にある、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','しま○ら') ;

2件レコードをINSERTする

例として、属性が全く違う商品をインサートしてみました。1つは、服飾、そしてもう1つは、ロボットですねw なんでも売っている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
) ;

2件とも無事に登録されているようです

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から特定の要素を抜き出す

先ほど登録した2件では、共通して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` |
+----+-------------------------------------+------------------------------------------------+

如何でしょうか?なんとなくスキーマレスとのハイブリッド構成が利用できる利点がご理解いただけたのではないかと思います。

以上、だいたいこのあたりが基本的な使い方になってくるかなーって思ってます。もっと高度な利用方法としては、 カラムのネスティングにも使えたりします。詳しい話しにつきましては

f:id:hit10231023:20160510112622p:plain

https://mariadb.com/kb/en/mariadb/dynamic-columns/

を参考にどうぞ