ネットで見つけた便利な方法。
MySQL でシリアライズされたデータを SELECT で抽出する
Selecting Data from a Serialized Array in MySQL
仕組みがややこしいので理解してみました。
SQL文に説明を入れて、途中経過を表示するカラムを付けています。
題材は「WelcartのSKUデータから価格だけ取得して安い順に並び替える」です。
この方法を使った実践編があります。
Welcart商品一覧ページで価格が高い・安い順に並び替え。二度手間なし一発データ取得
SQL文
/* SKUの価格(売価＝price)が安い順に並び替え */
SELECT
`meta_id`,
`post_id`,
`meta_key`,
/* meta_valueに保存されているシリアライズデータから売価(price)の値だけを抽出するカラム */
/* 第1階層 CONVERT関数を実行 */
CONVERT( /* 取得した売価の値を数値型に変換 */
/* 第2階層 SUBSTRING_INDEX関数を実行 */
SUBSTRING_INDEX( /* 区切り文字列"\""の-1個目(右側から1個目)までの文字列を取り出す */
/* 第3階層 SUBSTRING_INDEX関数を実行 */
SUBSTRING_INDEX( /*区切り文字列"\""の2個目までの文字列を取り出す*/
/* 第4階層 SUBSTRING関数を実行 */
SUBSTRING( /* 文字列(meta_valueの値＝シリアライズデータ)から位置を指定して文字列を取り出す */
`meta_value`, /* SUBSTRING関数の第1引数(文字列) */
/* 第5階層 "price"が最初に出現する位置に"price"の文字数を加算して、"price"の次の文字;の位置を求める(SUBSTRING関数の第2引数(開始位置)になる) */
( INSTR( /* 特定の文字列("price")が最初に出現する位置を調べる */
`meta_value`, /* INSTR関数の第1引数(検索対象の文字列) */
CONCAT( "\"", 'price', "\";" ) /* INSTR関数の第2引数(検索文字列"price"); */
) /* INSTR関数の戻り値("price"が最初に出現する位置) */
+
CHAR_LENGTH( 'price' ) /* priceの文字数(5文字)を加算 */
+ 2 /* 前後にある"の文字数(2文字)を加算 */
) /* SUBSTRING関数の第2引数(開始位置) ＝ "price"の次の文字;の位置 */
), /* 第3階層SUBSTRING_INDEX関数の第1引数(文字列) ＝ SUBSTRING関数の戻り値("price"の次の;の以降の文字列) */
"\"", /* 第3階層SUBSTRING_INDEX関数の第2引数(区切文字列) */
2 /* 第3階層SUBSTRING_INDEX関数の第3引数(数値N) ＝ 区切文字列2個目までの文字列を取得 */
), /* 第2階層SUBSTRING_INDEX関数の第1引数(文字列) ＝ 第3階層SUBSTRING_INDEX関数の戻り値("price"直後の;～priceの数値データ末尾までの文字列) */
"\"", /* 第2階層SUBSTRING_INDEX関数の第2引数(区切文字列) */
-1 /* 第2階層SUBSTRING_INDEX関数の第3引数(数値N) ＝ 区切文字列右側から1個目までの文字列を取得 */
), /* CONVERT関数の第1引数 ＝ 第2階層SUBSTRING_INDEX関数の戻り値(シリアライズデータから取り出した売価の値) */
SIGNED /* CONVERT関数の第2引数(データ型) */
) AS `price`, /* カラム名をpriceとする */
/* [途中経過を表示]第3階層のSUBSTRING_INDEX関数の実行結果を表すカラム */
SUBSTRING_INDEX( /*区切り文字列"\""の2個目までの文字列を取り出す*/
SUBSTRING( /*文字列(meta_valueの値＝シリアライズデータ)から位置を指定して文字列を取り出す*/
`meta_value`, /*SUBSTRING関数の第1引数(文字列)*/
( INSTR( /*特定の文字列("price")が最初に出現する位置を調べる*/
`meta_value`, /*INSTR関数の第1引数(検索対象の文字列)*/
CONCAT( "\"", 'price', "\";" ) /*INSTR関数の第2引数(検索文字列"price");*/
) + CHAR_LENGTH( 'price' ) + 2 /*(5文字+前後の"の2文字=)7を加算*/
) /*SUBSTRING関数の第2引数(開始位置)*/
), /*SUBSTRING_INDEX関数の第1引数(文字列) ＝ SUBSTRING関数の戻り値("price"以降の文字列が返る)*/
"\"", /*SUBSTRING_INDEX関数の第2引数(区切文字列)*/
2 /*SUBSTRING_INDEX関数の第3引数(数値N)*/
) AS `first_SUBSTRING_INDEX`, /*SUBSTRING関数の戻り値("price"以降の文字列が返る)*/
/* [途中経過を表示]第4階層のSUBSTRING関数の実行結果を表すカラム */
SUBSTRING( /*文字列(meta_valueの値＝シリアライズデータ)から位置を指定して文字列を取り出す*/
`meta_value`, /*SUBSTRING関数の第1引数(文字列)*/
( INSTR( /*特定の文字列("price")が最初に出現する位置を調べる*/
`meta_value`, /*INSTR関数の第1引数(検索対象の文字列)*/
CONCAT( "\"", 'price', "\";" ) /*INSTR関数の第2引数(検索文字列"price");*/
) + CHAR_LENGTH( 'price' ) + 2 /*(5文字+前後の"の2文字=)7を加算*/
) /*SUBSTRING関数の第2引数(開始位置)*/
) AS `SUBSTRING`, /*SUBSTRING関数の戻り値("price"以降の文字列が返る)*/
`meta_value`
FROM `wp_postmeta`
WHERE `meta_key` = '_isku_' /* SKUデータのみ取得 */
ORDER BY `price` ASC; /* 価格が安い順で並び替え(ORDER BY句ではエイリアスが使える) */取得元meta_valueのシリアライズデータ（例）
a:9:{s:4:"code";s:9:"WDP-CI-TE";s:4:"name";s:17:"チーク（TE）";s:6:"cprice";s:4:"2640";s:5:"price";s:4:"1500";s:4:"unit";s:0:"";s:8:"stocknum";s:3:"100";s:5:"stock";s:1:"0";s:2:"gp";s:1:"0";s:4:"sort";s:1:"0";}上記データから、価格の値「s:5:”price”;s:4:”1500″;」の「1500」のみをピンポイントで取得する例を説明します。
取得の仕組み・流れ
1. 取得する値の「””で囲まれたkey文字列」が最初に出現する位置を調べる
（第5階層）
つまり、「”price”」という文字列が最初に出現する位置を取得する。
INSTR関数で求める。
INSTR( 検索対象文字列, 検索文字列 )
戻り値：検索対象文字列内で、検索文字列が最初に出現する位置（数値）
INSTR( `meta_value`, CONCAT( "\"", 'price', "\";" ) )2. 取得した位置に「”price”」の文字数（7文字）を加算する
（第5階層）
つまり、「”price”」の直後にある文字「;」の位置（数値）を取得する。
1で取得した数値に「”price”」の文字数（7文字）を足し算する。
( INSTR関数の戻り値 + CHAR_LENGTH( 'price' ) + 2 )3. 「”price”」直後の「;」以降の文字列を取得する
（第4階層）
SUBSTRING関数で求める。
SUBSTRING( 文字列, 開始位置, 文字数 )
戻り値：文字列の開始位置から文字数分を取り出した部分文字列
※文字数を指定しない場合は文字列の最後まで取得する。
SUBSTRING( `meta_value`, 2で取得した「;」の位置の数値 );s:4:"1500";s:4:"unit";s:0:"";s:8:"stocknum";s:3:"100";s:5:"stock";s:1:"0";s:2:"gp";s:1:"0";s:4:"sort";s:1:"0";}4. 3で取得した文字列から、2個目の「”」までの文字列を取得する
（第3階層）
つまり、priceの数値の直後にある「”」直前までの文字列を取得する。
SUBSTRING_INDEX関数で求める。
SUBSTRING_INDEX( 文字列, 区切り文字, 数値N )
戻り値：文字列の中に含まれる区切り文字N個目までを取り出した部分文字列
SUBSTRING_INDEX( 3で取得した文字列, "\"", 2 );s:4:"15005. 4で取得した文字列から、右側から1個目の「”」までの文字列を取得する
（第2階層）
つまり、priceの数値だけを取得する。
SUBSTRING_INDEX関数で求める。
引数の数値Nが負数の場合は文字列の末尾から探し、見つかった位置よりも後の文字列を取得する。
SUBSTRING_INDEX( 4で取得した文字列, "\"", -1 )15006. 取得したpriceの数値を数値型データに変換する
（第1階層）
CONVERT関数
CONVERT( 値, データ型 )
戻り値：データ型に変換した値
CONVERT( 5で取得した文字列, SIGNED ) /* SIGNED＝数値型 */
コメントを残す