MySQLでPHPのシリアライズされたデータ内の検索をする方法

WordPressのメタデータやプラグインが独自に保存するデータにはたしばしばphpのserialize関数でシリアライズされた状態で格納されていることがあります。

unserializeすると簡単に配列や、キー・バリュー形式に戻せるので楽なんですが、そのデータの中をSQLで検索かけようとしたり、アップデートをしようとすると結構手間です。
そもそも最初っからそんなデータ設計するなってことなんですが、自分で作ったものではなく、前出のWordpressみたいに既存のデータを外部から利用したい場面もたまにあります。

MySQLだから、きっとSERIALIZE、UNSERIALIZEみたいな関数があるんだろうな、と高を括って探してみたらない！
そこで慌ててググった結果、ないんですね。（汗）
しかし、いくつか条件はあるものの、MySQLのSUBSTRING_INDEX関数を組み合わせてできることがここに出てました。

要は、まずシリアライズされたデータを”;”で区切り、区切られた目的の個数目のデータをさらに”:”で区切ると、その最後の要素が目的のデータになるということです。
なので、これを実現するにはシリアライズされるデータは常に同じ並び順でなければならず、データの中に”;”や”:”が紛れ込んでいたらデータがずれてしまいます。

【例1】
データ：reservationというテーブルのclientというカラムに以下のようなデータが保存されていたとします。
a:4:{s:4:”name”;s:6:”ナカマ”;s:5:”phone”;s:11:”09085852020″;s:5:”email”;s:17:”nakama@xxxx.co.jp”;s:2:”id”;s:3:”123″;}

これをemailがnakama@xxxx.co.jpという値で検索をしようとすると、SQLは以下のようになります。

SELECT * FROM reservation WHERE 
REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 6 ) , ':', -1 ) , '"', '' ) = 'nakama@xxxx.co.jp';

emailの値は全体を”;”で分割すると6番目に入っているので6が使われています。
0から始まらず1からですね。
’email’というキー名とは関連付けられてません。あくまで”;”で区切った先頭要素からの順番です。
SUBSTRING_INDEXで区切りると、最後の値は-1番目になります。
REPLACEで”を削除してるのは、”;”や”:”で区切っただけではデータが”で囲まれてるからです。

【例2】
reservationテーブルのclientから目的のデータを成形した形で取り出す場合は、同様にすればできます。
下の例はnameの値だけを取り出しています。

SELECT REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 2 ) , ':', -1 ) , '"', '' ) AS name
FROM reservation;

あんまり使いたくないですが、もうデータが出来上がってるものなら、データを取り出してphpでunserializeしてから目的の値を取り出すよりはスマートではないかと思います。

↓こちらにアップデートの方法も追加しました。

MySQLでPHPのシリアライズされたデータ検索の例文追加