Symfoware

Symfowareについての考察blog

MariaDB 列にハッシュ形式でデータを格納する(Dynamic Columns)

MariaDBのドキュメントで、「Dynamic Columns」という機能を知りました。

Dynamic Columns

これは動的に項目を増減したい場合に使えそう。



Dynamic Columns Basics



ドキュメントに記載されている内容を試してみます。
Dynamic Columnsとして使用する列のデータ型に「blob」を指定してテーブルを作成。


  1. create table assets (
  2.     item_name varchar(32) primary key,
  3.     dynamic_cols blob
  4. );




データを登録する際、COLUMN_CREATEを使用して列名と値を指定します。
[フィールド1], [値1], [フィールド2], [値2] ...
の順で指定します。

以下のクエリーで、


color = blue
size = XL



という値を登録することになります。


  1. INSERT INTO assets VALUES
  2. ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));




同様にもう1レコード。


color = black
price = 500




  1. INSERT INTO assets VALUES
  2. ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));




データを検索してみると、dynamic_colsにバイナリな値が設定されています。

670_01.png


データの取り出しには「COLUMN_GET」を使用します。


COLUMN_GET([フィールド名], [COLUMN_CREATEで指定たフィールド名] as [データ型])





  1. SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets;



670_02.png

列が存在しない場合はNULLになります。


  1. SELECT item_name, COLUMN_GET(dynamic_cols, 'size' as char) AS size FROM assets;



670_03.png


カラムの削除は「COLUMN_DELETE」


  1. UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price")
  2. WHERE COLUMN_GET(dynamic_cols, 'color' as char)='black';




カラムの追加は「COLUMN_ADD」


  1. UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
  2. WHERE item_name='Thinkpad Laptop';








COLUMN_EXISTS



カラムが存在しているかをチェックするには「COLUMN_EXISTS」を使用します。
存在すれば1、存在しなければ0を返します。


  1. SELECT COLUMN_EXISTS(dynamic_cols, 'color') FROM assets;



670_04.png



  1. SELECT COLUMN_EXISTS(dynamic_cols, 'size') FROM assets;



670_05.png




SELECT COLUMN_LIST



存在しているカラム名を知りたい場合は「COLUMN_LIST」


  1. SELECT COLUMN_LIST(dynamic_cols) FROM assets;



このように、各々の行で存在しているカラム名が取得できます。

670_06.png

戻り値は文字列のようです。





COLUMN_CHECK



指定したblob列が有効なDynamic Columnsであるかチェックします。
有効であれば1を、無効であれば0を返します。

Dynamic Columns列というものがあるわけではなく、blob列をDynamic Columnsとして
使用しているため、普通の文字列などもデータとして登録可能です。

試しにこんなクエリーでデータを追加。


  1. INSERT INTO assets VALUES
  2. ('test', 'normal string');




カラムをチェックしてみます。


  1. SELECT item_name, COLUMN_CHECK(dynamic_cols) FROM assets;



670_07.png

もちろん、先ほど登録したデータは0(無効)となります。




COLUMN_JSON



データをJSON形式に変換します。


  1. SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;



これは便利。

670_08.png


だだし、Dynamic Columnsとして有効ではない列が存在する場合、
こんなエラーが発生します。


SQL エラー (1919): Encountered illegal format of dynamic column string




実際に使用する場合は、COLUMN_CHECKと組み合わせた
こんなクエリーになると思います。


  1. SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets
  2. where
  3. COLUMN_CHECK(dynamic_cols) = 1;




ちなみに、値が空白の場合はエラーにならず空の文字列が返されます。
値がNULLだとエラーになりました。





Nesting dynamic columns



値のネストもサポートされています。


COLUMN_CREATE('parent_column', COLUMN_CREATE('child_column', 12345))




  1. INSERT INTO assets VALUES('test', COLUMN_CREATE('parent_column', COLUMN_CREATE('child_column', 12345)));




670_09.png



関連記事

テーマ:サーバ - ジャンル:コンピュータ

  1. 2016/02/21(日) 11:45:44|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<Alpine Linux nginx-luaをインストール | ホーム | MariaDB 10.1をAlpine Linuxにインストールする>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1852-f97f748b
この記事にトラックバックする(FC2ブログユーザー)