MySQLコマンドの出力をPHPでJSON形式に変換する

あらまし

ちょっとした野暮用でMySQLのSELECT結果をJSON形式で取得することになった。

きっと探せば良い感じのツールがあるんだろうけど、探したりインストールしたりするのが面倒なので、既にLinuxサーバにインストール済みのPHPででっちあげてみた。

前提

PHPのバージョン

$ php -r 'echo PHP_VERSION;'
5.3.3

mysqlコマンドの出力をパイプで渡すとTSV形式になるっぽい。

$ mysql -h DBHOST -u DBUSER -p DBNAME -e 'select year(now()),month(now()),day(now())'
+-------------+--------------+------------+
| year(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
|        2015 |           11 |         27 |
+-------------+--------------+------------+

$ mysql -h DBHOST -u DBUSER -p DBNAME -e 'select year(now()),month(now()),day(now())' | cat
year(now()) month(now())    day(now())
2015    11  27

でっちあげる

標準入力で受け取ったTSVデータをJSON形式に変換するPHPスクリプト

こんな感じ

• TSVデータの1行目はJSONのキー名にする
• TSVデータの2行目以降をJSONの値にする

$ mysql -h DBHOST -u DBUSER -p DBNAME -e 'SELECT code,name,kana,roman FROM prefs' | php -r '$ls=explode("\n",file_get_contents("php://stdin")); $k=explode("\t",array_shift($ls)); $m=array_map(function($r)use($k){return array_combine($k,explode("\t",$r));},array_filter($ls)); echo json_encode($m);'

出力結果

[{"code":"01","name":"\u5317\u6d77\u9053","kana":"\u307b\u3063\u304b\u3044\u3069\u3046","roman":"hokkaido"},{"code":"02","name":"\u9752\u68ee\u770c","kana":"\u3042\u304a\u3082\u308a\u3051\u3093","roman":"aomori"},{"code":"03","name":"\u5ca9\u624b\u770c","kana":"\u3044\u308f\u3066\u3051\u3093","roman":"iwate"},{"code":"04","name":"\u5bae\u57ce\u770c","kana":"\u307f\u3084\u304e\u3051\u3093","roman":"miyagi"},{"code":"05","name":"\u79cb\u7530\u770c","kana":"\u3042\u304d\u305f\u3051\u3093","roman":"akita"},{"code":"06","name":"\u5c71\u5f62\u770c","kana":"\u3084\u307e\u304c\u305f\u3051\u3093","roman":"yamagata"},{"code":"07","name":"\u798f\u5cf6\u770c","kana":"\u3075\u304f\u3057\u307e\u3051\u3093","roman":"fukushima"},{"code":"08","name":"\u8328\u57ce\u770c","kana":"\u3044\u3070\u3089\u304d\u3051\u3093","roman":"ibaraki"},{"code":"09","name":"\u6803\u6728\u770c","kana":"\u3068\u3061\u304e\u3051\u3093","roman":"tochigi"},{"code":"10","name":"\u7fa4\u99ac\u770c","kana":"\u3050\u3093\u307e\u3051\u3093","roman":"gunma"},{"code":"11","name":"\u57fc\u7389\u770c","kana":"\u3055\u3044\u305f\u307e\u3051\u3093","roman":"saitama"},{"code":"12","name":"\u5343\u8449\u770c","kana":"\u3061\u3070\u3051\u3093","roman":"chiba"},{"code":"13","name":"\u6771\u4eac\u90fd","kana":"\u3068\u3046\u304d\u3087\u3046\u3068","roman":"tokyo"},{"code":"14","name":"\u795e\u5948\u5ddd\u770c","kana":"\u304b\u306a\u304c\u308f\u3051\u3093","roman":"kanagawa"},{"code":"15","name":"\u65b0\u6f5f\u770c","kana":"\u306b\u3044\u304c\u305f\u3051\u3093","roman":"niigata"},{"code":"16","name":"\u5bcc\u5c71\u770c","kana":"\u3068\u3084\u307e\u3051\u3093","roman":"toyama"},{"code":"17","name":"\u77f3\u5ddd\u770c","kana":"\u3044\u3057\u304b\u308f\u3051\u3093","roman":"ishikawa"},{"code":"18","name":"\u798f\u4e95\u770c","kana":"\u3075\u304f\u3044\u3051\u3093","roman":"fukui"},{"code":"19","name":"\u5c71\u68a8\u770c","kana":"\u3084\u307e\u306a\u3057\u3051\u3093","roman":"yamanashi"},{"code":"20","name":"\u9577\u91ce\u770c","kana":"\u306a\u304c\u306e\u3051\u3093","roman":"nagano"},{"code":"21","name":"\u5c90\u961c\u770c","kana":"\u304e\u3075\u3051\u3093","roman":"gifu"},{"code":"22","name":"\u9759\u5ca1\u770c","kana":"\u3057\u305a\u304a\u304b\u3051\u3093","roman":"shizuoka"},{"code":"23","name":"\u611b\u77e5\u770c","kana":"\u3042\u3044\u3061\u3051\u3093","roman":"aichi"},{"code":"24","name":"\u4e09\u91cd\u770c","kana":"\u307f\u3048\u3051\u3093","roman":"mie"},{"code":"25","name":"\u6ecb\u8cc0\u770c","kana":"\u3057\u304c\u3051\u3093","roman":"shiga"},{"code":"26","name":"\u4eac\u90fd\u5e9c","kana":"\u304d\u3087\u3046\u3068\u3075","roman":"kyoto"},{"code":"27","name":"\u5927\u962a\u5e9c","kana":"\u304a\u304a\u3055\u304b\u3075","roman":"osaka"},{"code":"28","name":"\u5175\u5eab\u770c","kana":"\u3072\u3087\u3046\u3054\u3051\u3093","roman":"hyogo"},{"code":"29","name":"\u5948\u826f\u770c","kana":"\u306a\u3089\u3051\u3093","roman":"nara"},{"code":"30","name":"\u548c\u6b4c\u5c71\u770c","kana":"\u308f\u304b\u3084\u307e\u3051\u3093","roman":"wakayama"},{"code":"31","name":"\u9ce5\u53d6\u770c","kana":"\u3068\u3063\u3068\u308a\u3051\u3093","roman":"tottori"},{"code":"32","name":"\u5cf6\u6839\u770c","kana":"\u3057\u307e\u306d\u3051\u3093","roman":"shimane"},{"code":"33","name":"\u5ca1\u5c71\u770c","kana":"\u304a\u304b\u3084\u307e\u3051\u3093","roman":"okayama"},{"code":"34","name":"\u5e83\u5cf6\u770c","kana":"\u3072\u308d\u3057\u307e\u3051\u3093","roman":"hiroshima"},{"code":"35","name":"\u5c71\u53e3\u770c","kana":"\u3084\u307e\u3050\u3061\u3051\u3093","roman":"yamaguchi"},{"code":"36","name":"\u5fb3\u5cf6\u770c","kana":"\u3068\u304f\u3057\u307e\u3051\u3093","roman":"tokushima"},{"code":"37","name":"\u9999\u5ddd\u770c","kana":"\u304b\u304c\u308f\u3051\u3093","roman":"kagawa"},{"code":"38","name":"\u611b\u5a9b\u770c","kana":"\u3048\u3072\u3081\u3051\u3093","roman":"ehime"},{"code":"39","name":"\u9ad8\u77e5\u770c","kana":"\u3053\u3046\u3061\u3051\u3093","roman":"kochi"},{"code":"40","name":"\u798f\u5ca1\u770c","kana":"\u3075\u304f\u304a\u304b\u3051\u3093","roman":"fukuoka"},{"code":"41","name":"\u4f50\u8cc0\u770c","kana":"\u3055\u304c\u3051\u3093","roman":"saga"},{"code":"42","name":"\u9577\u5d0e\u770c","kana":"\u306a\u304c\u3055\u304d\u3051\u3093","roman":"nagasaki"},{"code":"43","name":"\u718a\u672c\u770c","kana":"\u304f\u307e\u3082\u3068\u3051\u3093","roman":"kumamoto"},{"code":"44","name":"\u5927\u5206\u770c","kana":"\u304a\u304a\u3044\u305f\u3051\u3093","roman":"oita"},{"code":"45","name":"\u5bae\u5d0e\u770c","kana":"\u307f\u3084\u3056\u304d\u3051\u3093","roman":"miyazaki"},{"code":"46","name":"\u9e7f\u5150\u5cf6\u770c","kana":"\u304b\u3054\u3057\u307e\u3051\u3093","roman":"kagoshima"},{"code":"47","name":"\u6c96\u7e04\u770c","kana":"\u304a\u304d\u306a\u308f\u3051\u3093","roman":"okinawa"}]

変換結果が非常に見づらい...

jqコマンドに渡すと見やすい

phpの出力をさらにjqコマンドに渡すと人間にやさしい感じになった。

$ mysql -h DBHOST -u DBUSER -p DBNAME -e 'SELECT code,name,kana,roman FROM prefs' | php -r '$ls=explode("\n",file_get_contents("php://stdin")); $k=explode("\t",array_shift($ls)); $m=array_map(function($r)use($k){return array_combine($k,explode("\t",$r));},array_filter($ls)); echo json_encode($m);' | jq -M '.'

出力結果

[
  {
    "roman": "hokkaido",
    "kana": "ほっかいどう",
    "name": "北海道",
    "code": "01"
  },

  ....

  {
    "roman": "okinawa",
    "kana": "おきなわけん",
    "name": "沖縄県",
    "code": "47"
  }
]