id:dekapurio
ポイントあり520ptベストアンサーあり
ウェブ制作

MYSQLテーブルのUPDATEについて


DBからTABLEAを読み込んでおいて、そのデータを違うテーブルに格納したい（常にデータをIDごとに最新の1件としたい）と思い、
以下URLを参考にやってみたのですが、うまく動いてくれません。
http://naruhodo.television.co.jp/qa4380807.html?check_ok=1
プログラムについてご教授願ください。
[MYSQL5.1 PHP5.2]

// 接続
require_once("conn.php");

// SQL
$result = mysql_query("SELECT id, name, sum(price) AS price FROM TABLEA GROUP BY id");

// 変数格納＆DB書き換え
while ($row = mysql_fetch_array($result)) {
$ins_sql[] = "UPDATE TABLEA SET
id = '".$row[id]."',
name = '".$row[name]."',
price = '".$row[price]."'";
}

for ($i=0; $i < count($ins_sql); $i++){
$res = mysql_query($ins_sql[$i]);

回答の条件
  • 1人3回まで
  • 登録：
  • 終了：2010/03/18 19:58:51
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:Km1967 No.5

回答回数224ベストアンサー獲得回数35

ポイント300pt

標準的SQLであれば、面倒なのだがテーブルＢに既存のid=1にはUPDATE、テーブルＢに存在しないid=2にはINSERTで対処するという二段階になる。

だがMySQLにはINSERTとUPDATEの両方を併せ持つようなREPLACEというものが用意されておるから１発だ。


まずは、テーブルＡからid毎の最新の一件を抽出する事を考える。これは以前のものの再利用だ。以前よりシンプルですむぞ。

SET @id = '', @c = 0;

SELECT id, date, price
FROM (
    SELECT *
        , IF(@id <> id, @c := 1, @c := @c + 1) c
        , IF(@id <> id, @id := id, @id := @id) i
    FROM テーブルＡ
    ORDER BY id, date DESC
) a
WHERE c = 1;

最新を抜き出せる事が確認できたなら本番だ。上記に１行加えるだけだ。

SET @id = '', @c = 0;

REPLACE INTO テーブルＢ
SELECT id, date, price
FROM (
    SELECT *
        , IF(@id <> id, @c := 1, @c := @c + 1) c
        , IF(@id <> id, @id := id, @id := @id) i
    FROM テーブルＡ
    ORDER BY id, date DESC
) a
WHERE c = 1;

http://dev.mysql.com/doc/refman/5.1/ja/replace.html


REPLACEを使う場合に注意せねばならぬのはPRIMARY KEYだ。以下の状態を想定しておる。

CREATE TEMPORARY TABLE テーブルＢ(id INT, date INT, price INT, PRIMARY KEY(id));

id毎に最新の１件しか入れぬのだからキーはidだけで十分という事だ。

テーブルＡのPRIMARY KEYは今回は問題とはならぬが、処理速度面からインデックスはついているほうがいいだろう。

id:dekapurio

回答有難うございます。

素晴らしいです。結果、パーフェクトでした。

UPDATEも、新規IDに対してのINSERTもできていました。

REPLACE構文はわたしの教科書には無いものでしたので、とても勉強になりました。

2010/03/18 19:54:05

その他の回答（4件）

id:taknt No.1

回答回数13539ベストアンサー獲得回数1198

ポイント100pt

基本的に 何がうまく行かないのか書いてもらったほうがいい。

ここで なんとかというエラーが出るとか・・・。

で質問のプログラムを見ると 違うテーブルに格納すると言いながら

＞UPDATE TABLEA SET

となっています。

これでは TABLEA に格納されてしまうことになりますので、最低限 テーブル名を別のものに

したほうがいいでしょう。

http://dev.mysql.com/doc/refman/4.1/ja/update.html

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

[ORDER BY ...]

[LIMIT row_count]

id:dekapurio

>ここで なんとかというエラーが出るとか

phpを実行するとエラーは出ませんが、データがDBへ格納されていない状態です。

>これでは TABLEA に格納されてしまうことになります

申し訳ないですが、これも記載ミスです。

･･･AS price FROM TABLEB GROUP BY id

が正解でした。

2010/03/18 14:44:23
id:ko8820 No.2

回答回数1221ベストアンサー獲得回数69

ポイント20pt

>phpを実行するとエラーは出ませんが、データがDBへ格納されていない状態です

明示的にcommitを発行してみては？

あと、UPDATEはすでにレコードがない場合は更新されません。

今回の場合はINSERT文を使うのでは？

id:dekapurio

>明示的にcommitを発行してみては？

ごめんなさい、当方初心者なもので意味が解りません。

>今回の場合はINSERT文を使うのでは？

INSERT文でも上書きできますかね？

2010/03/18 15:17:42
id:aside No.3

回答回数339ベストアンサー獲得回数31

ポイント50pt 
// 接続
require_once("conn.php");
// SQL
$result = mysql_query("SELECT id, name, sum(price) AS price FROM TABLEB GROUP BY id");
// 変数格納＆DB書き換え
while ($row = mysql_fetch_array($result)) {
  $rs = mysql_query("SELECT COUNT(*) FROM TABLEA WHERE id = '".$row[id]."'");
  $cnt = 0;
  while ($row = mysql_fetch_array($rs)) { $cnt++; }
  if ($cnt) {
    $res = mysql_query("UPDATE TABLEA SET id = '".$row[id]."',name = '".$row[name]."',price = '".$row[price]."'");
  } else {
    $res = mysql_query("INSERT INTO TABLEA (id,name,price) VALUES ('".$row[id]."','".$row[name]."','".$row[price]."')");
  }
}
id:dekapurio

回答ありがとうございます。

書き方等大変参考になりました。

2010/03/18 19:17:21
id:aside No.4

回答回数339ベストアンサー獲得回数31

ポイント50pt

これをまねよう

http://q.hatena.ne.jp/1255584952

http://q.hatena.ne.jp/1164377582

id:dekapurio

同じような質問が出ていましたね。

参考になりました。

2010/03/18 19:18:29
id:Km1967 No.5

回答回数224ベストアンサー獲得回数35ここでベストアンサー

ポイント300pt

標準的SQLであれば、面倒なのだがテーブルＢに既存のid=1にはUPDATE、テーブルＢに存在しないid=2にはINSERTで対処するという二段階になる。

だがMySQLにはINSERTとUPDATEの両方を併せ持つようなREPLACEというものが用意されておるから１発だ。


まずは、テーブルＡからid毎の最新の一件を抽出する事を考える。これは以前のものの再利用だ。以前よりシンプルですむぞ。

SET @id = '', @c = 0;

SELECT id, date, price
FROM (
    SELECT *
        , IF(@id <> id, @c := 1, @c := @c + 1) c
        , IF(@id <> id, @id := id, @id := @id) i
    FROM テーブルＡ
    ORDER BY id, date DESC
) a
WHERE c = 1;

最新を抜き出せる事が確認できたなら本番だ。上記に１行加えるだけだ。

SET @id = '', @c = 0;

REPLACE INTO テーブルＢ
SELECT id, date, price
FROM (
    SELECT *
        , IF(@id <> id, @c := 1, @c := @c + 1) c
        , IF(@id <> id, @id := id, @id := @id) i
    FROM テーブルＡ
    ORDER BY id, date DESC
) a
WHERE c = 1;

http://dev.mysql.com/doc/refman/5.1/ja/replace.html


REPLACEを使う場合に注意せねばならぬのはPRIMARY KEYだ。以下の状態を想定しておる。

CREATE TEMPORARY TABLE テーブルＢ(id INT, date INT, price INT, PRIMARY KEY(id));

id毎に最新の１件しか入れぬのだからキーはidだけで十分という事だ。

テーブルＡのPRIMARY KEYは今回は問題とはならぬが、処理速度面からインデックスはついているほうがいいだろう。

id:dekapurio

回答有難うございます。

素晴らしいです。結果、パーフェクトでした。

UPDATEも、新規IDに対してのINSERTもできていました。

REPLACE構文はわたしの教科書には無いものでしたので、とても勉強になりました。

2010/03/18 19:54:05

コメント（12件)

  • id:dekapurio
    dekapurio 2010/03/18 14:26:07
    最後の } が質問文に記載されていませんでした。
  • id:taknt
    きゃづみぃ 2010/03/18 14:34:53
    よくよく見てみると 突っ込みどころが 多すぎる・・・。

  • id:taknt
    きゃづみぃ 2010/03/18 14:43:50
    UPDATEじゃなくて INSERT文？
    あと マージが使えればマージ。

    UPDATEの時は、更新したいレコードを指定しないといけない。
    Where なんたらってね。

    あと 配列に 一回格納しないで そのまま実行しちゃえばいいのにね。
  • id:dekapurio
    dekapurio 2010/03/18 14:56:20
    INSERT文にすると毎日データが増えていきますよね？
    そもそもその辺りがまだよくわかっていないのですが･･

    マニュアルに「更新対象のレコードが指定されていない場合は、すべてのレコードが更新されます。」
    とあったのでWhereは必要ないと思っていました。

    >あと 配列に 一回格納しないで そのまま実行しちゃえばいいのにね。
    確かにそうですが、そのやり方がわかっていません。。
  • id:taknt
    きゃづみぃ 2010/03/18 15:03:22
    ＞マニュアルに「更新対象のレコードが指定されていない場合は、すべてのレコードが更新されます。」
    ＞とあったのでWhereは必要ないと思っていました。

    すべてのレコードがみな同じ 値になります。
    それでいいのならば、ループしないで 最後のレコードだけやっちゃえばいい。


    ＞>あと 配列に 一回格納しないで そのまま実行しちゃえばいいのにね。
    ＞確かにそうですが、そのやり方がわかっていません。。

    コネクションを もうひとつ作って 実行させればいいだけです。

  • id:Km1967
    Km1967 2010/03/18 17:06:20
    よくよく見なくても、突っ込みどころ満載の回答にコメントだ。
    SQLに惑わされず、冒頭にある目的だけを見据えてやればいい。

    その点、パパパフゥは流石じゃな。経験値が違いすぎる。だが、読みすぎだ。答えはもっとシンプルだ。
    MySQLの場合UPDATEとINSERTの２回に振り分ける必要すらもないぞ（注意事項は書いておいた）
  • id:Km1967
    Km1967 2010/03/18 20:13:08
    おお、サンプルデータを載せるのを忘れておった。
    意味は通じたようでよかったが恥ずかしい文面になっておるｗ
  • id:chuken_kenkou
    chuken_kenkou 2010/03/19 00:05:38
    ベストアンサーのSQLは、「全件検索して通番を付ける」、さらに「その通番を使って操作」という、「SQLとしてはいろいろ応用の利く書き方」ですが、性能面では「非常に効率の悪い操作」をわざわざ行っています。母体件数が相当に少ない場合や、趣味で行っている場合を除いては、あまり勧めできないと思います。

    これは毎回、具体的なコードを回答されているKm1967さんなら、皮肉などではないことはお分かりいただけると思います。

    また、MySQLの制限なのですが、複数列インデクスでの昇順・降順の混在は実装されていません。
    例えば、

    PRIMARY KEY(c1 ASC,c2 DESC)

    といったキーにした場合、MySQLでは、

    PRIMARY KEY(c1,c2)

    のように、勝手に変更します。
    これは、
    SHOW CREATE TABLE 表名
    で、定義済の表定義を表示させると確認できます。

    これがどのように影響するかというと、
    ORDER BY id,`date` DESC
    といった操作で、インデクスが活用できず、作業ファイルを使用してのソートが発生してしまいます。
    したがって、これをデータの絞込みを行えていない状態で行えば、性能劣化の大きな要因になります。
  • id:Km1967
    Km1967 2010/03/19 01:07:47
    id:chuken_kenkou さん。補足ありがとう。
    ただ、ますますMySQLが他に劣る点を強調してしまってるような気がするのは気のせいか。
    http://dev.mysql.com/doc/refman//5.1/ja/create-index.html
    >>
    ASC か DESC で終わる事ができます。これらのキーワードは昇順や降順インデックス値ストレージを指定する為の将来の拡張子として許容されます。現在は、それらは解析されますが無視されます。インデックス値は毎回昇順で格納されます。
    <<

    ＞あまり勧めできないと思います。
    前回（ http://q.hatena.ne.jp/1268119392#c174023 ）も今回も動いてるから、どうでもいいといえばどうでもいいのだけど、対策例も無しに言われたら気になるわ。
  • id:chuken_kenkou
    chuken_kenkou 2010/03/20 00:28:35
    「id毎の最大dateを持つ行の、他の列も得る」といった場合、selectの部分だけですが、

    select * from tableA
    where (id,`date`) in(select id,max(`date`) from tableA group by id)

    のような書き方をするのが、より一般的ではないでしょうか。

    ただ、MySQLでは、MySQL 5.0でようやくサブクエリの実装で、オプティマイザもまだまだ開発途上なのはご存知の通りです。まだまだ、ジョインに書き換えた方が、遥かに速いケースもたくさんあります。

    私の環境はMySQL 5.1.36ですが、母体件数=5120、idの値の種類=1024でEXPLAINの結果を見たところ、次のような書き方に変えた方が、インデクスが活用されています。

    select *
    from tableA as a1
    inner join (select id,max(`date`) as `date` from tableA group by id) as a2
    on (a1.id,a1.`date`)=(a2.id,a2.`date`)



    以下、参考までに検証に利用したコードです。

    dekapurioさんも、性能を気にするようでしたら、参考にしてみてください。



    drop table if exists tableA;
    drop table if exists tableB;

    create table tableA
    (id int
    ,`date` int
    ,`price` int
    ,primary key(id,`date`)
    );
    create table tableB
    (id int
    ,`date` int
    ,`price` int
    ,primary key(id)
    );

    insert into tableA values
    (1,1,100),(1,2,200),(1,3,300),(1,4,400),(1,5,500)
    ,(1,6,100),(1,7,200),(1,8,300),(1,9,400),(1,10,500)
    ;
    insert into tableA select id+1,`date`,`price` from tableA where `date`<=5;
    insert into tableA select id+2,`date`,`price` from tableA where `date`<=3;
    insert into tableA select id+4,`date`,`price` from tableA where `date`<=8;
    insert into tableA select id+8,`date`,`price` from tableA;
    insert into tableA select id+16,`date`,`price` from tableA;
    insert into tableA select id+32,`date`,`price` from tableA;
    insert into tableA select id+64,`date`,`price` from tableA;
    insert into tableA select id+128,`date`,`price` from tableA;
    insert into tableA select id+256,`date`,`price` from tableA;
    insert into tableA select id+512,`date`,`price` from tableA;

    explain
    select * from tableA
    where (id,`date`) in(select id,max(`date`) from tableA group by id)
    ;

    explain
    select *
    from tableA as a1
    inner join (select id,max(`date`) as `date` from tableA group by id) as a2
    on (a1.id,a1.`date`)=(a2.id,a2.`date`)
    ;


  • id:chuken_kenkou
    chuken_kenkou 2010/03/20 00:34:00
    連続で失礼。

    MySQLでのサブクエリ実装は、MySQL 4.1でした。



  • id:Km1967
    Km1967 2010/03/20 01:47:01
    検証ご苦労様。
    テキストなどでもよく見る手法だし、それを否定する気などないが、今回はインデックス利用の有無に気を取られすぎだと思うぞ。
    「ソートを要するシーケンシャルスキャン」と「ランダムアクセスを要するシーケンシャルスキャン」では、どっちがハードディスクやメモリを効率よく使ってくれるかという点を洗い出して整理したほうがいいだろう。端的に説明することは出来ぬがMySQLのメモリ管理とOSのメモリ管理を学べばわかってくるはずだ。
    それが結果として「数分放置しても動かなかった」と「9秒かかりましたが、ついに動きました。」の差となって表れてくるのだし、SQLを組む際の指針ともなる。チューニングともなればなおさらだ。
    http://q.hatena.ne.jp/1268119392#c174023

    終了した質問で長々と話を引っ張る訳にもいかぬから、以上で一旦終了とさせていただく。

    こんなサービス滅多にしないんだからね！　シェリルノーム風に（マクロスフロンティアって結構面白れぇな）

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

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

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

回答リクエストを送信したユーザーはいません
ユーザー登録をして使いはじめる!無料
すでに登録されている方はこちらからログイン
この質問に含まれるキーワード
知りたいことを検索してみよう
▲ページトップへ