MySQLでJSON
MySQL5.7 から JSON のカラムが使えるようになりましたので色々と試してみました。以下では jcol をJSON型のカラムとして説明します。
UPDATE
JSON_SET
UPDATE tbl SET jcol=JSON_SET(jcol, '$.type', 'hogehoge') WHERE id=1
tbl(テーブル) の id=1 の jcol(JSON型カラム) の type(JSONのキー名) に 'hogehoge' を設定します。 つまり以下のようなJSONになります。
結果
SELECT jcol FROM tbl WHERE id=1
// 結果
{"type":"hogehoge"}
JSON_SET を使わず、直接文字列 で更新することもできます。
UPDATE tbl SET jcol='{"type":"hogehoge"}' WHERE id=1
JSON_REMOVE
JSON_REMOVE で JSONのキーを削除。
UPDATE tbl SET jcol=JSON_REMOVE(jcol, '$.type') ;
注意点(UPDATEの空振り?)
MySQL 5.7.18 の場合、jcol(JSONカラム) の初期値が NULL や 空 だとUPDATE しても 反映されません でした。またデフォルト値の設定もできませんでした。ので、 初回 INSERT時に jcol に '{}' を設定しています。
もしくは・・・ JSON_INSERT と JSON_UPDATE 同時にやると空振りはなくなるかも・・・
UPDATE tbl SET
jcol=JSON_SET(jcol, '$.$k.$u',1) ,
jcol=JSON_INSERT(jcol, '$.$k', JSON_OBJECT('$u', 1))
WHERE id='$id' " ;
SELECT
SELECT jcol FROM tbl WHERE id=1
// 結果
{"type":"hogehoge"}
SELECT jcol->'$.type' FROM tbl WHERE id=1
// 結果
"hogehoge"
これだと hogehoge とダブルクォーテーションがついてくるので
SELECT jcol->>'$.type' FROM tbl WHERE id=1
// 結果
hogehoge
->> にすることで hogehoge というダブルクォーテーション無しの値が取得できます。
WHERE句+JSON型カラム
SELECT * FROM tbl WHERE jcol->'$.type'='hogehoge'
SELECT jcol->>'$.type' as type FROM tbl WHERE jcol->'$.type'='hogehoge'
JSON_INSERT
UPDATE tbl SET jcol=JSON_INSERT(jcol, '$.fav', JSON_OBJECT('a', 1)) WHERE id=1
PHP でJSONを扱う
PHP には json_encode や json_decode といった関数があります。MySQLから取得したJSONデーターを json_decode すれば 連想配列 として扱いやすくなります。
php JSONを連想配列に
$arr = json_decode($json, true) ;
print_r($arr) ;
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 " ;
編集する
