[MySQL] ランダムでデータを取得

●ランダムで データを5件取得
select id from [table_name] ORDER BY RAND() limit 5;

[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'
 ;

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で複数テーブルもしくは特定のテーブルなど条件指定でレコードを出力する方法

クエリーキャッシュ関係を調べる

◎クエリーキャッシュが有効になっているか確認する

SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

◎キャッシュのサイズを確認する

show variables like 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+

「have_query_cache」が「YES」になってても、この↑キャッシュサイズが0だったら キャッシュはされない。

◎キャッシュサイズを設定する

SET GLOBAL query_cache_size = 41984;

my.cnfで設定

query_cache_size = 32M

設定後にMySQL再起動 ： service mysqld restart

◎クエリーキャッシュされたかを確認する

SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

キャッシュされると「Qcache_hits」とか「Qcache_queries_in_cache」とかの値が増える

文字列フィールドの 平均バイト数を出す

select avg(b.length) from (SELECT length(comment) as length from blogs where del_flg=0) as b;

auto_increment リセット

ALTER TABLE `table` PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_INCREMENT =1

※あちこちでみかけた、

「alter `table` test auto_increment=1;」

じゃ、出来なかった

参考）http://shoulder.jp/archives/007078.php

===========================
（追記）2012/10/24

テーブルに データが入ったままの場合は 別の方法で振り直す

オートインクリメント（自動連番）カラムの値を振り直す場合、下記の手順で実行する（例では ID カラムがこれに該当）

1. ID カラムを削除
   alter table TBL-NAME drop column ID;
   オートインクリメント・カラムは各テーブルに一つだけしか作成できないので、連番を振り直す場合は、一旦カラムを削除して新たに作り直す必要がある
2. 新規 ID カラム（整数型・自動連番）を追加
   alter table TBL-NAME add ID int(5) primary key not null auto_increment first;
   primary key は重複を許さない主キーのことであり、NOT NULL でなければならない

参考）
MySQL よく使うコマンド - WEB + PC http://weblogs.tail-lagoon.com/WebPC/2008/03/18/15/

MySQL 数値型 最大値とか最小値とか

型 バイト 最小値 最大値

TINYINT 1 -128 127

SMALLINT 2 -32768 32767

MEDIUMINT 3 -8388608 8388607

INT 4 -2147483648 2147483647

BIGINT 8 -9223372036854775808 9223372036854775807

参考)

mysqlのint(11)の11 - Clouder::Bloggerhttp://blog.clouder.jp/archives/000228.html

ファイルからSQL実行させる

mysql -A -h[host_name] -P[port] -u[user] -p[password] --table [DB_name] < [sql_file_path]

--table, -t

出力をテーブルフォーマットで表示します。インタラクティブの場合これがデフォルトになりますが、テーブル出力をバッチモードで生成するのに使用することもできます。

-Aオプションってなんだっけ・・・・。

◎sql_file の中身

[show_ps.sql] MySQL4

SHOW PROCESSLIST

[show_ps.sql] MySQL5

select * from information_schema.processlist where db=[db_name] and info not like 'select * from information_schema.processlist %'
↑これだとsleepのプロセスでなかった。ふつーにshow processlistでいいみたい。

ユーザーの作成・権限設定

流れ的には
ユーザ作って、権限与えて、アクセス確認
という感じ

●設定されている ユーザーの確認


mysql> SELECT host,user,Password FROM mysql.user;

●ユーザーの権限確認

mysql> show grants for 'root'@'localhost';

→今ログインしているユーザーの権限確認

mysql> show grants;

　・　同じ権限の新規ユーザを作るには、↑表示されるGRANTを 新規ユーザーに置き換えて実行してやればいい

参考：

http://www.dbonline.jp/mysql/user/index1.html

http://www.dbonline.jp/mysql/user/index6.html

MBA（Lion）にMySQLを入れる

MyMBAに MySQLを入れようとして 手詰まった

最初は gem install mysqlで入れようとしたんだけど
色々エラーが出るし、ぐぐっても あのコマンドオプションをつけろとか
これつけろとか 良く分かんない・・・
で、Homebrewから入れてみるか とも思ったけど
こっちも 入れた人のブログ見てると 一筋縄じゃいかない様子・・・うむむ


そんなむずかしくなくていいのよー
普通に本家からでいいから　入れたいのよー
でも 本家英語だらけだし、どれ入れていいのかわかんないのよー


（ここまでで半日費やしてる）


↓こちらのサイトを参考に

クライアント版のLionにはMySQLは入っていない @ アールケー開発 - http://www.rk-k.com/archives/1215

MySQLサーバDLページで
Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive　(mysql-5.5.22-osx10.6-x86_64.dmg)
を選択


DL前に なんか Emailとかpassとかいれるページが出てくるけど
» No thanks, just take me to the downloads!

↑このリンク選べばOK


DLした.dmgをダブルクリック
中に以下のファイルが入ってるので ダブルクリックでインストールで完了


・mysql-5.5.22-osx10.6-x86_64.pkg
　→本体　なにはともあれこれはインストール


・MySQL.prefPane
　→MySQLを「システム環境設定」から操作できるようにするもの

MySQLを起動したり、停止がここからできる（下のチェックボックスは、スタートアップの設定）

・MySQLStartupItem.pkg
　→MySQLをスタートアップに登録するもの（今回はMySQL.prefPane入れたので、こっちは入れなかった）




インストールが完了したら ターミナルから確認（各コマンドは/usr/local/mysql/bin/の下にある）

$ /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 390
Server version: 5.5.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.01 sec)

起動が確認できたら .bash_profileの＄PATHに/usr/local/mysql/bin/追加
#PATH MySQL
export PATH=$PATH:/usr/local/mysql/bin/

あと rootのパスワード設定

mysqladmin -u root password 'new_password_here'