CLというカラムの中に、100個の要素をカンマ区切りにして1つの文字列として入れてあるとします。100個の各要素の中身は空または任意の文字列(文字数も任意)です。
これを100個の要素それぞれの中身によって抽出したいです。
抽出条件としては、「何かしらの文字が入っている場合」「特定の文字にマッチする場合」「特定の文字のみが入っている場合」という3つのパターンを複合して指定します。
例えば、CLに入っている100要素の「$n[$a]番目が$xにマッチして、且つ$n[$b]番目が$xxで、且つ$n[$c]番目に何かしらの文字列がある場合」という感じで、複合条件に合致したものを抽出したいわけです。
このSELECT構文をperl側で書く場合、どのように書けばよいでしょうか。
もしカラムを100個作る形で処理するのであれば記述は分かるのですが、実際問題として100個のカラムを作るのは処理上どうなのかと思ってしまいます。その辺のこともお教え頂けると助かります。
わかりにくい説明でしたらすみません。どうかお教え下さいませ。
テーブル名が分からなかったため、sampleとしています。
以下のようなSQLで「例えば」で説明されていた検索を出来るはずです。
適当なテーブルを作成して実際に試してみましたが、Perlは詳しくないので、Perlを使っての稼動確認はできていません。
($xや$xxをシングルクォートでくくってしまっている部分あたりが怪しいかもしれません。)
SELECT * FROM sample org WHERE ( SELECT SUBSTRING_INDEX( ( SELECT SUBSTRING_INDEX( CL, ',', $n[$a] ) FROM sample tmp WHERE tmp.id = org.id ) , ',' , -1 ) FROM sample tmp WHERE org.id = tmp.id ) = '$x' AND ( SELECT SUBSTRING_INDEX( ( SELECT SUBSTRING_INDEX( CL, ',', $n[$b] ) FROM sample tmp WHERE tmp.id = org.id ) , ',' , -1 ) FROM sample tmp WHERE org.id = tmp.id ) = '$xx' AND ( SELECT SUBSTRING_INDEX( ( SELECT SUBSTRING_INDEX( CL, ',', $n[$c] ) FROM sample tmp WHERE tmp.id = org.id ) , ',' , -1 ) FROM sample tmp WHERE org.id = tmp.id ) != ''
基本的な考えとしては、2つのSUBSTRING_INDEXをネストすることで、SUBSTRING_INDEXでカンマ区切りで特定の出現位置の直前までの部分文字列を取得したものを対象にして、その文字列のカンマ区切りの最後の値を取得したものを比較の対象としています。
(例えば、'あああ,いいい,ううう,えええ,おおお'というような値に対して、SUBSTRING_INDEXで3つめの,まで、つまり'あああ,いいい,ううう'を抜き出し、その後、SUBSTRING_INDEXで-1を指定することで、右から数えて最初の,まで、つまり'ううう'を取得します。)
もっと良いやり方があるのかもしれませんが、このやり方ですと、副問い合わせを多様しているため、同じテーブルに対して何度もSELECT文が発行されてしまいますし、SUBSTRING_INDEXを使う場合、おそらくメモリ上には列の値全体を一度展開しているでしょうし、効率的ではないと思います。
普通に100個の列を作っておいた方が、パフォーマンスは良いはずです。
また、それぞれの項目の名称も管理できますし、どのフィールドに値がある、ない、などの判断も容易になります。
個人的な意見ですが、カンマ区切りのデータを1つの列に持たせるのではなく、列を分ける方が良いと思います。
詳しいご回答と助言を頂きましてありがとうございました!とてもわかりやすい内容で嬉しいです。