MySQL5.1の質問です


table_a
・sozai(りんご、みかん・・)

table_b
・name(りんごジュース、みかんジュース、りんごのお酒、みかんゼリー)
・sozai_desu(最初は何も入っていません)

以上のような2つのテーブルがあります。関連付けられているフィールドはありません。

sozaiの文字列がnameの文字列に含まれていた場合に、その時のsozaiの文字列をsozai_desuに格納したいです。

つまり「りんごジュース」「りんごのお酒」は「りんご」という文字列が含まれているのでsozai_desuフィールドに「りんご」を格納します。

これはTriggerにしかできないでしょうか?
データを格納するタイミングですが
・table_aに新しくレコードを追加
・table_bに新しくレコードを追加

この両方を考えています。
これが出来ると色々と応用が利きますので挑戦したいと思っています。
もしかすると複雑な仕組みになるかもしれないので予算ポイントは500以上用意します。

アドバイスだけでも構いませんのでどなたかガイドして頂けないでしょうか。

回答の条件
  • 1人5回まで
  • 登録:2007/04/03 14:48:33
  • 終了:2007/04/08 11:06:29

回答(2件)

id:b-wind No.1

b-wind回答回数3344ベストアンサー獲得回数4402007/04/03 18:45:08

ポイント50pt

まず、そのような処理はDBでやることでは無いです。

Trigger というかストアドプロシジャーを使えば不可能ではないですが、それはプログラムそのものなのでデータを投入するアプリケーションで処理してから投入した方が自然です。


あと素材が複数含まれている可能性は無いのですか?

table_b.sozai_desu に何が入るかも含めてテーブル設計を見直されたほうがよいように思います。

id:tokyosmash

うーんそうですねちょっと無理がありますよね。

日々データベースに蓄積される新しい商品を商品名から自動的にジャンル分けしたいのです。人間が手作業でやると効率が悪いので、プログラムに代わりをさせられると考えました。

最初にジャンル分けするキーワード(バナナ、いちご等)が決まっていればいいのですが、新しく生まれるジャンルもあるのでこのような仕組みで対応できないかな、と考えた次第です。

実際には商品ではありませんし、いちごもバナナも登場しない世界ですけど。

素材は僅か(1%未満)に重複する事がありますが、まあその辺は仕方ないと思っています。

別の方法であれば簡単にできるかもしれませんね。少し自分で考えてみます。アドバイスありがとうございました。

2007/04/03 19:16:49
id:kurukuru-neko No.2

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/04/03 22:07:47

ポイント500pt

無理やれば出来なくはないですが

日本語に対応した全文検索機能がないと

あまり性能は出ません。

登録の支援用ならTriggerとかで

やるよりは普通のプログラムでも

可能とは思います。

あまり精度がよい方法ではないので、

プログラム生成したものを手動で

最終調整してマスター登録する

ような目的には使える可能性が

あります。

但し、適正なキーワードで登録制度を

あげたい場合は、全文検索などで

文脈い依存の区切りで処理しないと

いい加減なものになりあまり効率がよいとは

いえません。

MYSQLで全文検索の日本語処理が

テストしてみて動作するようであれば

likeの処理を全文検索に対応させれば

多少は性能、結果もよくなる可能性があります。

http://qwik.jp/tritonn/about.html

参考にLikeでProcedureで処理した場合

SQLの部分をPHP等から呼ぶか、PHPで

書き換えれ出来ると思います。

table_a/table_bの相互に関連性が

ない状態でTRIGGERを処理時、自己参照

が必要になるので止めた方よい。


何らかの関係は定義して方が取扱

しやすとは思いますが。

table_a

key int primary key

table_b

key int primary key

sozai_desu_idx -> table_c(key)

table_c

key int not null auto_increment primary key,

parent_key int -> table_b(key)

child_key int -> table_a(key)

http://dev.mysql.com/doc/refman/4.1/ja/example-auto-increment.ht...

-- Create/Drop Table
--
drop table if exists table_a;
drop table if exists table_b;
create table if not exists table_a (
  sozai char(80) primary key
) default charset utf8 collate utf8_unicode_ci;

create table if not exists table_b (
  name char(80) primary key,
  sozai_desu text
) default charset utf8 collate utf8_unicode_ci;

--
-- Procedulre
--
DELIMITER ||

--- Table Aに追加
drop procedure IF EXISTS PROC_Add_table_a;
create procedure         PROC_Add_table_a(in in_key char(80) )
begin

  insert into table_a values(in_key);
  update table_b
     set sozai_desu=
      (select group_concat(distinct sozai order by sozai separator",") from table_a
          where name like concat("%",sozai,"%")
          limit 1)
  where name like concat("%",in_key,"%");
end;
||

--- Table Aから削除
drop procedure IF EXISTS PROC_Del_table_a;
create procedure         PROC_Del_table_a(in in_key char(80) )
begin
  update table_b
     set sozai_desu=
      (select group_concat(distinct sozai order by sozai separator",") from table_a
          where name like concat("%",sozai,"%") and sozai!=in_key
       limit 1)
  where name like concat("%",in_key,"%");
  delete from table_a where sozai=in_key;
end;
||

--- Table Aを変更
drop procedure IF EXISTS PROC_Chg_table_a;
create procedure         PROC_Chg_table_a(in in_new_key char(80),in in_old_key char(80) )
begin

  -- 古いキーを削除
  update
     table_b
     set sozai_desu=
      (select group_concat(distinct sozai order by sozai separator",") from table_a
          where name like concat("%",sozai,"%") and sozai!=in_old_key limit 1)
  where name like concat("*",in_old_key,"*");

  -- Table A 変更.

  update table_a set sozai=in_new_key where sozai=in_old_key;

  -- 新しいキーで再構築.
  update
     table_b
     set sozai_desu=
         (select group_concat(distinct sozai order by sozai separator",") from table_a
          where name like concat("%",sozai,"%") limit 1)
  where name like concat("%",in_new_key,"%");

end;
||


--- Table Bへ追加
drop procedure IF EXISTS PROC_Add_table_b;
create procedure         PROC_Add_table_b(in in_key char(80) )
begin
  insert into table_b
   values(in_key,
    (select group_concat(distinct sozai order by sozai separator ",") from table_a where
       in_key like concat("%",sozai,"%") limit 1)
   );
end;
||

--- Table Bから削除
drop procedure IF EXISTS PROC_Del_table_b;
create procedure         PROC_Del_table_b(in in_key char(80) )
begin
  delete from  table_b where name=in_key;
end;
||

--- Table B変更
drop procedure IF EXISTS PROC_Chg_table_b;
create procedure         PROC_Chg_table_b(in in_new_key char(80),in in_old_key char(80) )
begin
  update table_b set name=in_new_key,sozai_desu=
          (select group_concat(distinct sozai order by sozai separator ",")
                   from table_a where in_new_key like concat("%",sozai,"%") limit 1
           )
  where name=in_old_key;
end;
||


DELIMITER ;


select "------------------------------------------- " from dual;
select " Start Test ------------------------------- " from dual;
select "------------------------------------------- " from dual;

insert into table_a values
('ばなな'),
('オレンジ'),
('梨'),
('レモン');

insert into table_b values
('りんごジュース',NULL),
('みかんジュース',NULL),
('みかん&リンゴジュース',NULL),
('リンゴのお酒',NULL),
('ダイヤモンドゼリー',NULL),
('みかんゼリー',NULL);

select "-------- Add ---------" from dual;

call PROC_Add_table_a('みかん');
call PROC_Add_table_a('りんご');
call PROC_Add_table_b('梨&レモン');

select sozai from table_a;
select name,sozai_desu from table_b;

select "-------- Del ---------" from dual;
call PROC_Del_table_a('レモン');
call PROC_Del_table_a('みかん');
call PROC_Del_table_a('りんご');
call PROC_Del_table_b('梨&レモン');

select sozai from table_a;
select name,sozai_desu from table_b;

call PROC_Add_table_a('みかん');
call PROC_Add_table_a('りんご');
call PROC_Add_table_b('梨&レモン');

select "------------";
call PROC_Chg_table_a('ダイヤモンド','みかん');
call PROC_Chg_table_b('いちご&ミカン&リンゴ&ダイヤモンド','りんごジュース');
call PROC_Add_table_a('いちご');

select sozai from table_a;
select name,sozai_desu from table_b;

  • id:tokyosmash
    質問文だけでは伝わっていないかもしれないのでここで補足させて頂きます。またみなさんのイメージが少しでも沸き易いように具体的な例も挙げます。

    1)table_bにレコードを追加した場合
    これはtriggerじゃなくてもできるのかな?と思ってます。
    新しい商品として「バナナジュース」を追加します。つまりnameに追加する事になります。

    この時に、table_a.sozaiに「バナナ」を含むレコードがあれば、table_b.sozai_desuに「バナナ」を格納します。
    もし無い場合はそのまま空白のままです。

    2)table_aにレコードを追加した場合
    table_b.name いちごジュース
    table_b.sozai_desu NULL

    このようなレコードがあるとします。
    つまり現時点ではtable_a.sozaiに「いちご」が無いわけです。
    この状態でtable_a.sozaiに「いちご」を加えると上のtable_b.sozai_desuに「いちご」が格納されます。

    つたない日本語ですが、イメージ頂けたでしょうか?
  • id:b-wind
    > 人間が手作業でやると効率が悪いので、プログラムに代わりをさせられると考えました。
    プログラムで行う事は正しいです。ただ、この場合それはDBではなくアプリケーションで行うべきです。
    まさか、DBを直接ユーザーが触るわけではないですよね?

    > 素材は僅か(1%未満)に重複する事がありますが
    RDMBS は型にはめる事を得意とする反面、1%と言えども例外は許容できません。
    全ての素材は重複しうると考えて設計するべきです。
  • id:kurukuru-neko
    group_concatは長さ制限があるので
    文字数がおそらく1024が設定されている
    と思います。それ以上の長さの場合は
    設定変更が必要。
    mysql> show variables like "gro%";

    ★★★★★★★★★★★★★★★★
    ★★★ 注意 ★★★★★★★★★
    ★★★★★★★★★★★★★★★★

    上記の回答では最初に
    table_a/b をdrop しています。

    ※:誤字脱字当て字がたくさんありすいません。
  • id:tokyosmash
    詳細な回答ありがとうございます。
    MySQLが壊れてしまったようで、作業ができなくなりました。
    今しばらくお待ち下さい。

    いつもありがとうございます。
  • id:tokyosmash
    Triggerやプロシージャを使う方法はちょっと複雑&敷居が高いので、皆さんのアドバイスどおり別の方法を考えてみました。

    1.
    ユーザーが各商品ページにアクセスした時に、table_b.sozai_desuに何も入っていなければtable_a.sozai検索

    2.
    もしヒットすればUPDATE文でtable_b.sozai_desuに入れる

    これでいいんじゃないかと思いました。
    質問文のように
    ・table_aに新しくレコードを追加
    ・table_bに新しくレコードを追加
    のタイミングで実行され無くなりますけど。
    あくまでユーザーのアクセスをきっかけとして動く事になります。

    実際に試して見ましたが、問題なく動作しているようです。データの量が多くなった時のパフォーマンスが心配ですけど、数千件程度なのでなんとかなると思います。

    何でもかんでもDB上でやるのはナンセンスなんだと勉強になりました。
  • id:kurukuru-neko

    考え方を変えて
    登録の手間を減らすのが目的であれば
    table_a/b登録時に、検索して
    登録の重複なく99%程度の確率であるなら
    自動的に候補を画面に表示して問題なければ
    登録、問題あれば選択しなおして登録すれば
    よいと思います。
    1%の訂正で済むか、99%が入力作業
    の簡略化出来るかは大きな違いになる。
    (作る方は面倒でしょうけど)

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

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

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

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