perlでのmysql4.0へのSELECT構文で質問です。

CLというカラムの中に、100個の要素をカンマ区切りにして1つの文字列として入れてあるとします。100個の各要素の中身は空または任意の文字列(文字数も任意)です。
これを100個の要素それぞれの中身によって抽出したいです。
抽出条件としては、「何かしらの文字が入っている場合」「特定の文字にマッチする場合」「特定の文字のみが入っている場合」という3つのパターンを複合して指定します。
例えば、CLに入っている100要素の「$n[$a]番目が$xにマッチして、且つ$n[$b]番目が$xxで、且つ$n[$c]番目に何かしらの文字列がある場合」という感じで、複合条件に合致したものを抽出したいわけです。
このSELECT構文をperl側で書く場合、どのように書けばよいでしょうか。
もしカラムを100個作る形で処理するのであれば記述は分かるのですが、実際問題として100個のカラムを作るのは処理上どうなのかと思ってしまいます。その辺のこともお教え頂けると助かります。
わかりにくい説明でしたらすみません。どうかお教え下さいませ。

回答の条件
  • 1人2回まで
  • 登録:2006/04/09 20:13:41
  • 終了:2006/04/09 22:58:03

回答(1件)

id:bonlife No.1

回答回数421ベストアンサー獲得回数752006/04/09 22:27:01

ポイント100pt

テーブル名が分からなかったため、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つの列に持たせるのではなく、列を分ける方が良いと思います。

id:kamikage

詳しいご回答と助言を頂きましてありがとうございました!とてもわかりやすい内容で嬉しいです。

2006/04/09 22:56:40
  • id:bonlife
    お風呂に入っていて思い出したのですが、カンマ区切りを1つの列にセットしている場合はINDEXの設定ができませんね。
    列を分けて、検索の条件になる項目にはINDEXを作成しておくと良いでしょう。
    また、100個の値の利用頻度が一様でないのであれば、テーブルを利用頻度に応じて分割すると良いと思います。
    (たしかこういった分割をテーブルの垂直分割と呼んだ気がしますが、それに関する良い記事を見つけられませんでした。)
    検索の条件になる頻度が高い列をあつめたテーブルと、それ以外の列をあつめたテーブルにしておいて、結合して利用すればパフォーマンス向上を図れます。
    参考になると幸いです。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません