MySQL ストアドファンクションを利用してJSON形式のファイルを生成する

こんばんわ、まあ、たんなる文字列遊びなんですが、MySQLのストアドファンクションを利用して、JSON形式のデータにコンバートする関数を作ってみました。

つかってみて意外と便利だったので以下に掲載します。

【toJson ストアドファンクション】

DROP function toJSON;

DELIMITER //
CREATE FUNCTION toJSON(
  in_fieldname text,
  in_type int,
  in_value text,
  in_rec_end_flg int
)
 RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE _json TEXT DEFAULT '';
  DECLARE _delim CHAR(1);

  IF (in_rec_end_flg = 0) THEN
    SET _delim = ',';
  ELSE
    SET _delim = '';
  END IF;  

  CASE in_type
  WHEN 0 THEN
    SET _json = CONCAT('"',in_fieldname,'":',in_value,_delim);
  WHEN 1 THEN
    SET _json = CONCAT('"',in_fieldname,'":"',in_value,'"',_delim);
  ELSE
    SET _json = CONCAT('"',in_fieldname,'":',in_value,_delim);
  END CASE;

  RETURN _json;
END;
//
DELIMITER ;

SQLの利用方法はこんな感じになるかと・・いや。ならないかも？・・。

【SQL結果】

mysql> select
    -> CONCAT(
    ->   toJSON('TABLE_SCHEMA',1,TABLE_CATALOG,0),
    ->   toJSON('TABLE_NAME',1,TABLE_NAME,0),
    ->   toJSON('TABLE_TYPE',1,TABLE_TYPE,0),
    ->   toJSON('ENGINE',1,ENGINE,1)
    -> ) AS JSON_TEXT
    -> FROM
    ->  information_schema.TABLES limit 10;
+------------------------------------------------------------------------------------------------------------------------+
| JSON_TEXT                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------+
| "TABLE_SCHEMA":"def","TABLE_NAME":"CHARACTER_SETS","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY"                        |
| "TABLE_SCHEMA":"def","TABLE_NAME":"COLLATIONS","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY"                            |
| "TABLE_SCHEMA":"def","TABLE_NAME":"COLLATION_CHARACTER_SET_APPLICABILITY","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY" |
| "TABLE_SCHEMA":"def","TABLE_NAME":"COLUMNS","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MyISAM"                               |
| "TABLE_SCHEMA":"def","TABLE_NAME":"COLUMN_PRIVILEGES","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY"                     |
| "TABLE_SCHEMA":"def","TABLE_NAME":"ENGINES","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY"                               |
| "TABLE_SCHEMA":"def","TABLE_NAME":"EVENTS","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MyISAM"                                |
| "TABLE_SCHEMA":"def","TABLE_NAME":"FILES","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY"                                 |
| "TABLE_SCHEMA":"def","TABLE_NAME":"GLOBAL_STATUS","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY"                         |
| "TABLE_SCHEMA":"def","TABLE_NAME":"GLOBAL_VARIABLES","TABLE_TYPE":"SYSTEM VIEW","ENGINE":"MEMORY"                      |
+------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

色々と不完全な、部分はありますが、JSONの構造を詳しく知りたい方は、http://www.json.org/json-ja.html　を参照いただくと幸せになれるかもしれません。