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

先日MySQLでPHPでシリアライズされたデータ内をSQLだけを使って検索をする方法を紹介しましたが、その記事が意外と人気だったので、例文を追加します。
今回はMTS Simple Bookingビジネス版の持っているデータを対象に、自分のよく使うものをそのまんま載せます。

名前をキーに、客の氏名、メールアドレス、電話番号、予約回数を取り出すものです。

SELECT REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 4 ) , ':', -1 ) , '\"', '' ) , ' ', '' ) AS name, 
REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) AS tel, 
REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 12 ) , ':', -1 ) , '\"', '' ) , ' ', '' ) AS email, 
COUNT( booking_id ) AS count
FROM `wp_mtssb_booking`
WHERE REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 4 ) , ':', -1 ) , '\"', '' ) , ' ', '' ) LIKE '%中間%'
GROUP BY REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 4 ) , ':', -1 ) , '\"', '' ) , ' ', '' ),
REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' )

見た目無駄に長そうですが、5500件のデータから検索するのに、Xserver上で0.0148秒で実行できます。

次に、電話番号をキーに予約日、予約品目ID、氏名を取り出すもの。

SELECT REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 4 ) , ':', -1 ) , '\"', '' ) , ' ', '' ) AS name,
REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) AS tel,
DATE_FORMAT( FROM_UNIXTIME( `booking_time` -32400 ) , '%Y%m%d' ) AS DAY , article_id , created
FROM `wp_mtssb_booking`
WHERE REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) = '09085840000'

これも、0.0404秒で実行できます。

メールアドレスをキーに、電話番号が空のものに電話番号をセットしてアップデートする例。

UPDATE `wp_mtssb_booking` SET client = REPLACE(client, 's:3:"tel";s:0:"";', 's:3:"tel";s:11:"08053870000";')
WHERE REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( client, ';', 12 ) , ':', -1 ) , '\"', '' ) , ' ', '' ) = 'nakama@xxxxxx.co.jp';

集計的なもので、以下の例はユーザ登録しているのに、ログインなしで予約している人たちを取り出すもの。ユーザテーブルと予約テーブルを電話番号をキーにして参照しています。

SELECT DISTINCT u.ID, REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 4 ) , ':', -1 ) , '\"', '' ) , ' ', '' ) AS name, REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) AS tel, COUNT( b.booking_id ) AS count
FROM `wp_mtssb_booking` AS b
LEFT OUTER JOIN wp_usermeta AS um ON REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) = REPLACE( um.meta_value, '-', '' )
AND um.meta_key = 'mtscu_tel'
LEFT OUTER JOIN wp_users AS u ON u.ID = um.user_id
WHERE b.user_id =0
AND u.ID IS NOT NULL
AND REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) IS NOT NULL
GROUP BY REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' )
ORDER BY count DESC

そしてこれは、ユーザ登録をしないで予約をしてくれた方のリストです。
やはり、電話番号をキーにユーザテーブルに存在せず、予約テーブルに存在するものを検索します。

SELECT DISTINCT REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 4 ) , ':', -1 ) , '\"', '' ) , ' ', '' ) AS name, REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) AS tel, COUNT( b.booking_id ) AS count
FROM `wp_mtssb_booking` AS b
LEFT OUTER JOIN wp_usermeta AS um ON REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) = REPLACE( um.meta_value, '-', '' )
AND um.meta_key = 'mtscu_tel'
LEFT OUTER JOIN wp_users AS u ON u.ID = um.user_id
WHERE b.user_id =0
AND u.ID IS NULL
AND REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' ) IS NOT NULL
GROUP BY REPLACE( REPLACE( SUBSTRING_INDEX( SUBSTRING_INDEX( b.client, ';', 20 ) , ':', -1 ) , '\"', '' ) , '-', '' )
ORDER BY count DESC

こうやって、予約データの中を簡単にSQLで取り出せるのは非常に便利です。MTS Simple Bookingビジネス版のバックヤード機能を拡張するときに非常に役に立ちました。

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