人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

MySQLでデータの保存方法について質問です。

1つのフィールド内に「1,2,3」と言うようにカンマ区切りのデータを保存する箇所があります。通常、この部分は正規化して別テーブルに保存するのだと思いますが、今回はなんとか別テーブルにしなくて保存し、表示・検索(WHEREで抽出)する方法がないものかと思ってます。

検索する時は
"SELECT * FROM test_db WHERE user_id LIKE '%$user_id%' "

とすると、カンマ区切りのデータをヒットさせることが出来ますが、$user_idが「1」でも「11」でも、同じデータとしてヒットしてしまいます。

LIKEで検索しているから当然かと思うのですが、なんとかカンマ区切りのデータを分けて表示する方法はありませんでしょうか?

もし、「そういう仕様なので無理」と言う場合は、データの保存方法の代替え案をいただければと思います。

MySQLは4.0.26を使っています。


●質問者: kt26
●カテゴリ:ウェブ制作
✍キーワード:MySQL SELECT いもの いるか データ
○ 状態 :終了
└ 回答数 : 5/6件

▽最新の回答へ

1 ● tkyk3
●50ポイント

冗長性のデータをわざわざ別テーブル作ってしまうと困ってしまうというのは良くありますね。私もそういうデータをどうしようかと思っていたのですが、以下はSQLではなく、代替案としてのプログラム言語側からの方法です。

Perlの場合、

Class::DBI でもっと透過的に擬似的カラムを扱う

http://blog.livedoor.jp/nipotan/archives/50372194.html

のように、変数データをそのまま文字列化してData::Dumperなど使ってみるといいかもしれません。Perlですと上の方法を使ってみようと思っている次第です。こう言うのをシリアライズと言うそうです。

PHPですと、

$obj = new Object;
$line = serialize($obj);

のようにするとオブジェクトを1行の文字列に変換でき、

元に戻すには

$obj = unserialize($line);

とするようです。PHPは詳しくないので、「PHP シリアライズ」などで探してみてください…

お役に立てますことを願いつつ…はずしていたらすみません。

◎質問者からの返答

回答ありがとうございます。検索して調べてみます。


2 ● t_shiono
●50ポイント

そのまま検索するというのは、使ったことはないですが、MySQLのREGEXP関数とか使えばいけるかもしれません。

http://www.limy.org/program/db/mysql/mysql_operators.html

ただ、やはり最初と最後を考えると若干複雑になってしまうので、必ず最初と最後も区切り文字を入れるという形でデータを保持し、区切りも含めて条件を入れればいいんじゃないでしょうか?

例えば、1,2,3のデータを

*1*2*3*

としておくと、

WHERE user_id LIKE '%*$user_id*%'

でいけると思います。

また、検索時に、1と3を含むものを抽出するようなことをしたければ、からならずデータ中の順序を昇順(降順でもOK)にしておき、かつ区切りを、*#*のような対象な複数文字からなるようにすると、

データは

*#*1*#*2*#*3*#*

となり、次のようなクエリで抽出できるかと思います。

WHERE user_id LIKE '%*1*%*3*%'

もちろん、最初の方法でANDをとっても行けますが、こちらの方が速度的には早い気がします。


どちらも試してませんが、いかがでしょうか?

はずしていたら、すいません。

◎質問者からの返答

回答ありがとうございます。意味はわかります。


バックアップを取った時、ややこしい気もしますが、やはりこういう方法しかないのですかね。。


3 ● KUROX
●0ポイント

>通常、この部分は正規化して別テーブルに保存するのだと思いますが、

そういうこともありません。

カンマ区切りのデータの数は仕様で最大数を設定できませんか?

たとえば、200個までとか。

その場合は、単純にフィールドを200個作って、前から順番に入れるという方法を

とることもあります。

この方法で変だと思うのなら、別テーブルもちです。

カンマ区切りでデータをもって、Like検索する限りでは、

SQLだけでデータを絞り込めないし大量データのとき遅いと思いますけど。

ほかいっぱい、デメリットがありますけど。

フィールド数が増えるのは遅くなるとか考えてるのなら、今後は回答控えますけどね。

◎質問者からの返答

遅いとか何よりも非効率な気がしますね。

回答1の方も答えてくれていますが、わざわざテーブルを作るほどでもないのでは?

例を見てもそんなに大量のデータが発生すると、想定出来ないのではないでしょうか。


4 ● tkyk3
●10ポイント

下のコメント「RDBMS 的には別テーブルのほうが適切かつ効率的」で思い出したのですが、折角リレーショナルなのだから、

test_db

id club
1001 放送
1002 卓球
1003 美術
1004 帰宅

users

id test_db_id user_id
101 1001 1
102 1002 2
103 1002 3
104 1003 1
105 1003 11
106 1003 2
107 1003 4
108 1004 111

これで、あとは結合してやると、

SELECT test_db.id as test_db_id, club
FROM test_db, users
WHERE test_db.id = users.test_db_id
AND user_id = 1
test_db_id club
1001 放送
1003 美術

となると思います。こうすれば冗長性のあるデータも収められるかと思います。

◎質問者からの返答

質問にも書いていますが、別テーブルに分けて正規化する方法が良いのは重々承知しています。

しかし、必ずしもその必要が無いと判断した場合、もしくは「複数」という値が少ない場合は、

1つのフィールド内で納めるのでも良いのではないか?と思い、当質問をしました。


Googleで検索してもRDBMSだからといって必ずしも分ける必要はないという意見もあります。私もそう思っているので、「分けずに出来る方法はないものか?」と思い、質問させていただきました。


皆様のご指摘は重々理解しているつもりです。ですが、出来れば質問の意図を汲み取っていただければ良かったのですが…。


5 ● chuken_kenkou
●100ポイント ベストアンサー

'11'をヒットさせたい場合、

SELECT * FROM test_db WHERE user_id REGEXP '^11$|^11,|,11$|,11,'

といった方法はいかがでしょうか?

^11$→'11'しか入っていないケース

^11,→'11'が先頭にあるケース

,11$→'11'が末尾にあるケース

,11,→'11'が中間にあるケース

をor条件で検索しています。

◎質問者からの返答

まさにこれです!「1,2,11」というデータで試しましたが、user_idが1と11は別として扱われました。

元データを特に変更することもなかったです。


非常に参考になりました。ぜひ利用させていただきます。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ