作成
MySQLでJSON
MySQL5.7 から JSON のカラムが使えるようになりましたので色々と試してみました。以下では #K#jcol をJSON型のカラムとして説明します。 ### title UPDATE ###title3 JSON_SET ###source UPDATE tbl SET jcol=JSON_SET(jcol, '$.type', 'hogehoge') WHERE id=1 ###memo tbl(テーブル) の id=1 の jcol(JSON型カラム) の type(JSONのキー名) に 'hogehoge' を設定します。 つまり以下のようなJSONになります。 ###source (結果) SELECT jcol FROM tbl WHERE id=1 // 結果 {"type":"hogehoge"} ### memo JSON_SET を使わず、直接文字列 で更新することもできます。 ###source UPDATE tbl SET jcol='{"type":"hogehoge"}' WHERE id=1 ###title3 JSON_REMOVE ###memo JSON_REMOVE で JSONのキーを削除。 ###source UPDATE tbl SET jcol=JSON_REMOVE(jcol, '$.type') ; ###title3 注意点(UPDATEの空振り?) ###memo mini MySQL 5.7.18 の場合、jcol(JSONカラム) の初期値が NULL や 空 だとUPDATE しても #R#反映されません でした。またデフォルト値の設定もできませんでした。ので、 初回 INSERT時に jcol に '{}' を設定しています。 ### memo もしくは・・・ JSON_INSERT と JSON_UPDATE 同時にやると空振りはなくなるかも・・・ ###source UPDATE tbl SET jcol=JSON_SET(jcol, '$.$k.$u',1) , jcol=JSON_INSERT(jcol, '$.$k', JSON_OBJECT('$u', 1)) WHERE id='$id' " ; ### title SELECT ### memo 以下のようなデーターがある場合。 ###source SELECT jcol FROM tbl WHERE id=1 // 結果 {"type":"hogehoge"} ###source SELECT jcol->'$.type' FROM tbl WHERE id=1 // 結果 "hogehoge" ### memo これだと #*#"hogehoge" とダブルクォーテーションがついてくるので ###source SELECT jcol->>'$.type' FROM tbl WHERE id=1 // 結果 hogehoge ### memo #*#->> にすることで #*#hogehoge というダブルクォーテーション無しの値が取得できます。 ###title3 WHERE句+JSON型カラム ###source SELECT * FROM tbl WHERE jcol->'$.type'='hogehoge' SELECT jcol->>'$.type' as type FROM tbl WHERE jcol->'$.type'='hogehoge' ###title3 JSON_INSERT ###source UPDATE tbl SET jcol=JSON_INSERT(jcol, '$.fav', JSON_OBJECT('a', 1)) WHERE id=1 ### title PHP でJSONを扱う ### memo PHP には json_encode や json_decode といった関数があります。MySQLから取得したJSONデーターを #*#json_decode すれば #*#連想配列 として扱いやすくなります。 ### source (php JSONを連想配列に) $arr = json_decode($json, true) ; print_r($arr) ; ### source (php 連想配列をJSONに) $json = json_encode($arr, JSON_HEX_TAG|JSON_HEX_AMP|JSON_HEX_APOS|JSON_HEX_QUOT|JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE) ; // jcol の全キーが更新されます。 $sql = " UPDATE tbl SET jcol='$json' WHERE id=1 " ;
wakatta 記法
投稿の仕方
基本的な記法
エンジニア向け記法
記法のサンプル
このコンテンツを見る