MySQL でシリアライズされたデータを SELECT で抽出する

MySQLでシリアライズされたデータをSELECTで抽出する際に、便利な方法があったので、その備忘録的な投稿になります。

クライアントよりデータベースに格納されているデータから、指定の項目を抽出してCSVでエクスポートしたいという要望って結構あるんですが、今回のケースは、WordPressのデータベースからエクスポートする案件だったのですが、指定の項目がシリアライズされたデータの中にあったので、どうしようか迷いました。

WordPress案件だったので、シリアライズされたデータは、WordPress関数もしくは、PHPを用いれば、簡単に表示・エクスポート機能を持たせたページを作成することが可能です。そこで表示確認やダウンロードしてもらうことで用件を満たすことはできたのですが、データがすぐ欲しかったので、MySQLのSELECTコマンドでなんとかならないかと調べていました。

SUBSTRING_INDEXを使用してSELECTで一発取得ができた

SELECTコマンド一発で簡単に抽出することができないか、いろいろ調べてみたところ、ダウンロード用のページ作成なんかしなくても、SUBSTRING_INDEXを使用してSELECTで一発取得することができた。

結論から言うと、以下のようなSQLコマンドになります。

SELECT
    u.ID,
    SUBSTRING_INDEX( 
        SUBSTRING_INDEX( 
            SUBSTRING(um.meta_value, ( INSTR( um.meta_value, CONCAT( 'pref', '";' )  ) + CHAR_LENGTH( 'pref') + 1 ) ), 
        '"', 2 ),
    '"', -1 ) AS pref
FROM wp_users AS u
    LEFT JOIN wp_usermeta AS um
    ON u.ID = um.user_id
    AND um.meta_key = 'submitted'
WHERE u.ID > 1
ORDER BY u.ID ASC

Googleで検索していたら、こちらのサイトで紹介されていました。
ありがとうございます！

Selecting Data from a Serialized Array in MySQL via @polevaultweb