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を使っています。

回答の条件
  • 1人2回まで
  • 登録:2007/11/03 17:48:04
  • 終了:2007/11/04 20:15:25

ベストアンサー

id:chuken_kenkou No.5

chuken_kenkou回答回数722ベストアンサー獲得回数542007/11/04 19:28:31

ポイント100pt

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

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

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

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

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

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

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

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

id:kt26

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

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


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

2007/11/04 20:13:45

その他の回答(5件)

id:tkyk3 No.1

tkyk3回答回数59ベストアンサー獲得回数62007/11/03 20:04:02

ポイント50pt

冗長性のデータをわざわざ別テーブル作ってしまうと困ってしまうというのは良くありますね。私もそういうデータをどうしようかと思っていたのですが、以下は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 シリアライズ」などで探してみてください…

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

id:kt26

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

2007/11/04 00:53:09
id:t_shiono No.2

t_shiono回答回数256ベストアンサー獲得回数222007/11/03 22:56:22

ポイント50pt

そのまま検索するというのは、使ったことはないですが、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をとっても行けますが、こちらの方が速度的には早い気がします。


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

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

id:kt26

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


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

2007/11/04 00:55:33
id:KUROX No.3

KUROX回答回数3542ベストアンサー獲得回数1402007/11/04 08:57:06

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

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

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

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

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

とることもあります。

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

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

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

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

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

id:kt26

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

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

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

2007/11/04 14:00:22
id:tkyk3 No.4

tkyk3回答回数59ベストアンサー獲得回数62007/11/04 18:34:50

ポイント10pt

下のコメント「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 美術

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

id:kt26

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

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

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


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


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

2007/11/04 18:50:05
id:chuken_kenkou No.5

chuken_kenkou回答回数722ベストアンサー獲得回数542007/11/04 19:28:31ここでベストアンサー

ポイント100pt

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

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

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

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

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

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

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

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

id:kt26

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

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


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

2007/11/04 20:13:45

質問者が未読の回答一覧

 回答者回答受取ベストアンサー回答時間
1 onotorape 6 5 0 2007-11-04 19:21:54
  • id:b-wind
    >遅いとか何よりも非効率な気がしますね。
    >回答1の方も答えてくれていますが、わざわざテーブルを作るほどでもないのでは?
    RDBMS 的には別テーブルのほうが適切かつ効率的。
  • id:kt26
    入る値が10個以下の場合もですか?

    私には別テーブルにしたり10フィールド追加して、変更を加える点が増えるよりも、
    1つのフィールド内で処理出来る方法があれば、それを使いますけどね。
    「システム的な効率化」と言うよりは人的な効率化と言いますか。

    それにあくまで質問は「1つのフィールド内に」という条件なわけですし…
    別テーブルに分けて正規化しないパターンをお聞きしているわけですから。
  • id:tkyk3
    1の方法は、恐らくブログシステム「WordPress」とかでもやっている手法だと思います。最初データベースの中を見て???でしたが、シリアライズを知って納得しました。

    4の方法を登録しましたが、別テーブルパターンなので、ちょっと質問読み違えていました。すみません、開かなくて結構です…。
  • id:tkyk3
    あっ、遅かった・・・

  • id:kt26
    tkyk3さん

    もう開いてしまいましたが、、私も他のCMSを見ながら最適な方法はないか?皆さんはどうしているのかな?と思って質問した次第です。ですので、tkyk3さんのご意見は大変参考になりました。

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

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

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

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