[MySQL] テンポラリーテーブル＆「INSERT ... ON DUPLICATE KEY UPDATE」構文

集計バッチで
MySQLのテンポラリーテーブルと
「INSERT ... ON DUPLICATE KEY UPDATE」構文の合わせ技を使ったので
メモ

まず テンポラリーテーブルを使うにあたって、MySQLのメモリ関係の設定値を確認する

参考) MySQLのメモリ関係のシステム変数 - 祈れ、そして働け ～ Ora et labora http://d.hatena.ne.jp/tetsuyai/20111006/1317873012

メモリ上に作成される テンポラリファイルは max_heap_table_size内まで
超えるとISAMテーブルとしてディスクに書き出されます。

(デフォ)max_heap_table_size グローバル 16MB

それから、今現在のDBのテーブルデータサイズから

テンポラリーテーブルのおよそのデータサイズを出す

参考）データベースとテーブルのサイズを確認する方法 - ふってもハレても
http://d.hatena.ne.jp/sho-yamasaki/20120405/1333640589

DB調べたら

Avg_row_length＝151で、200としても

平均4万レコードくらいの データをテンポラリーテーブルにするから

200x4万レコード=800万byte → 7.63MB ・・・もんだいなすっぽ

で、
テンポラリーテーブルを使った 処理について

まず データの出力イメージは
商店Aテーブル、商店Bテーブル があります
それぞれの テーブルから 日付を絞って、商品番号でGroupByした集計を取得して、
それをマージします

↓こんな感じ

┌────┬───┬───┐
│商品番号│商店A │商店B │
├────┼───┼───┤
│   11111│     3│     2│
├────┼───┼───┤
│   11112│     0│     5│
├────┼───┼───┤
│   11113│     2│     0│
└────┴───┴───┘

まず 商店Aテーブルを集計して テンポラリーテーブルに データインサート

CREATE TEMPORARY TABLE `tmp_summary` (
  `商品番号` varchar(10) NOT NULL,
  `商店A購買数` int(11) NOT NULL DEFAULT 0,
  `商店B購買数` int(11) NOT NULL DEFAULT 0,
   PRIMARY KEY (`商品番号`)
)
  SELECT  商品番号 ,count(商品番号) as 商店A購買数, 0 as 商店B購買数
  FROM    商店Aテーブル
  WHERE   del_flg       =  0 
  AND     購買日   >= 'YYYY/MM/DD 00:00:00'
  AND     購買日   <= 'YYYY/MM/DD 23:59:59'
  GROUP BY 商品番号
  ;

SELECT文内の 「0 as 店舗B 購買数」のところは
次に集計する 店舗Bの購買数が入る部分を あらかじめ
０で埋めて先に作っとく的な感じ

次に 商店Bテーブルを集計して
先ほどのテンポラリーテーブル「tmp_summary」に マージしていきます
この時 「INSERT ... ON DUPLICATE KEY UPDATE」構文を使うと
MySQLが
既にPKが存在している時は UPDATE
なかったら INSERTしてくれます

INSERT INTO tmp_summary 
  SELECT  商品番号 , 0 as 商店A購買数, count(商品番号) as 商店B購買数
  FROM    商店Bテーブル
  WHERE   del_flg       =  0 
  AND     購買日   >= 'YYYY/MM/DD 00:00:00'
  AND     購買日   <= 'YYYY/MM/DD 23:59:59'
  GROUP BY 商品番号

  ON DUPLICATE KEY UPDATE
  商店A購買数=tmp_summary.商店A購買数+values(商店A購買数),
  商店B購買数=tmp_summary.商店B購買数+values(商店B購買数);

これで あとは テンポラリーテーブルをよしなに処理すればOK

select * from tmp_summary;

参考）
複合UNIQUEキーでも「INSERT ... ON DUPLICATE KEY UPDATE」構文は使える - 岩本隆史の日記帳
http://d.hatena.ne.jp/IwamotoTakashi/20080329/p1

テンポラリテーブルの作成MySQL｜プログラムメモ
http://logic.moo.jp/memo.php/archive/11/

他のテーブルのデータを追加(INSERT ... SELECT文) - データの追加と削除 - MySQLの使い方
http://www.dbonline.jp/mysql/insert/index6.html

SELECT結果からテーブルを作る

↓こんな感じのね

CREATE TABLE [table_name]
 SELECT  *
 FROM    TBL_A
 WHERE   del_flg       =  0 
 AND     regist_date   <= '2012/07/23 23:59:59'
 ;

jQueryで同じIDを持つ 複数要素を選択する

<div id="hoge">hoge</div>
<div id="hoge">hoge</div>
<div id="hoge">hoge</div>

↑だったら

$("[id=hoge]").html('fuga');


参考）
$("[id=bar]"). http://www.kinopyo.com/blog/jquery-select-multy-element-with-same-id

コンポーネントでモデルを使うとき [cakePHP2]

↓メソッドの中で 使うモデルを定義しましょう

  public function component_method()
  {

    // Model呼び出し
    $this->controller->loadModel('UserInfo');
    $user = $this->controller->UserInfo->find("first",
              array("conditions"=>array(
　　：
　　：

mysqldump いろいろ

 ・特定のテーブル(複数可)のレコードのみダンプ　テーブル作成情報は出さない

$ mysqldump -u [username] -p[password] -t [database_name] [table_name1] [table_name2]・・・> [file_path]

・データベース全体のテーブル構造のみ ダンプ 　レコード情報を一切書き込まない)

$ mysqldump [username] -p[password] -d [database_name] > [file_path]

→ createtableとinsert文も一緒に出すときは −dオプションを外す

参考）
MySQLのダンプ（エクスポート）、インポート、バックアップ - Tips and Memo 

mysqldumpで複数テーブルもしくは特定のテーブルなど条件指定でレコードを出力する方法