PakuReserve R&D Laboratory

DBの設計上、NULLが入っている場合の集計の際にちょっとハマりました。

下記のような注文テーブル(orders)があったとします。

DESC orders;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| total     | int(11) | YES  |     | NULL    |                |
| sub_total | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

SELECT * FROM orders;
+----+-------+-----------+
| id | total | sub_total |
+----+-------+-----------+
|  1 |  1050 |       500 |
|  2 |  1575 |      NULL |
+----+-------+-----------+

各注文のtotalからsub_totalを差し引いた金額(エイリアスをcostとする)を取得したい時、

SELECT (total - sub_total) AS cost FROM orders;

このようにすると、NULLの場合は0として計算してくれるだろうと思っていたら甘かったです。
id: 1のcostは550、id: 2のcostはNULLとなります。

SELECT (IFNULL(total,0) - IFNULL(sub_total,0))
AS cost FROM orders;

NULLが入っている可能性がある場合は、このようにIFNULLを使ってやればNULLならば0として計算してくれます。

IFNULL(expr1,expr2)

expr1がNULLでない場合はexpr1を返し、それ以外の場合はexpr2を返します。

チョコボール MySQL, データベース

・フォームから送信された日付をDBにINSERT、UPDATEできる形にするための整形
・DBからSELECTで引っ張ってきたDATETIME型の日付の整形

上記を例として日付の整形ってよく出食わします。
sprintf、substr、explode、正規表現を使う等、いろんなやり方があると思いますが…
自分が使っている方法をまとめてみます。

※例 DATETIME型の文字列を整形して表示

$time = '2008-07-10 21:02:23';
$data = sscanf($time, '%d-%d-%d %d:%d:%d');

sscanf()はprintf()の入力版で、文字列を指定したフォーマットに基づいて配列で抜き出します。
(フォーマットについてはprintf()のマニュアル参照)

$dataは下記のようになります

Array
(
    [0] => 2008
    [1] => 7
    [2] => 10
    [3] => 21
    [4] => 2
    [5] => 23
)

一発で変数に入れたい場合はlist()関数を使いましょう。

list($year, $month, $day, $hour, $minute, $second) =
	sscanf($time, '%d-%d-%d %d:%d:%d');
echo $year.'年'.$month.'月'.$day.'日 '.$hour.'時'.$minute.'分'.$second.'秒';

//【結果】2008年7月10日 21時2分23秒

こんな感じにすれば取り敢えず整形して表示できますが、一桁の月、日はゼロで穴埋めしたかったりする場合もあったりするので、ここは関数sprintf()を使って整形します。

echo sprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	$year, $month, $day, $hour, $minute, $second);

//【結果】2008年07月10日 21時02分23秒

sprintf()の引数では配列を指定できませんが、vsprintf()を使うと配列を指定でき、

list($year, $month, $day, $hour, $minute, $second) =
	sscanf($time, '%d-%d-%d %d:%d:%d');
echo sprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	$year, $month, $day, $hour, $minute, $second);

の二行を一行でやっつけちゃえます。

echo vsprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	sscanf($time, '%d-%d-%d %d:%d:%d'));

関数やメソッドで用意しておくと使い回しできて良いかと思われます。

チョコボール MySQL, PHP, Tips, データベース

お疲れ様です
のびーにょです

今日はDBオペレート時の話
自分はUPDATE文やDELETE分流す時には指差し確認を行っています。

なぜかって
怖いから

昔、本番環境のDB触ってるときにUPDATE文流す時に、”WHERE”句を付けずに実行した事があります。
会員のゲーム状況を保存しているテーブルで、それが一気に全部書き変わっちゃって・・・

あの時のあせりと言ったらもう、やばかったですよ。

幸い200件ほどしかテーブルにデータが入っていなかった(ゲームごとに違うテーブルでリリース直後だった)ので
手作業で戻すことができました。

また、上司が間違ってBBSのデータをdropしたことがありました。
あの時はバックアップから復旧したはずですけど、バックアップ取ってから書き込まれた内容は消えてしまっています。

そういう経験を見たり、自分でやっちゃってからは必ず指差しで”WHERE”句が付いていることを確認してから実行しています。

変更する物を先にSELECTで変更する内容、件数を確認してSELECT文を改変してUPDATE、DELETE文にするのもいい方法です。

それでも指差し確認してますけど。

皆様もDBを操作する場合には十分ご注意ください。

と、言っても自身がミスしない限りはきっと無理なんだろうなぁと思います。

プログラム組んでるとDBなんて日常的に操作する物だから、慣れで適当になってしまいますよね・・・

それでも自分で指差し確認をしているのはあの恐怖をもう二度と経験したくないからです。

皆さんも頭の片隅に入れておいて下さいね。

のびーにょ Tips, データベース

とあるプログラムで気になったことをメモ。

PCか携帯か判断する関数。
DBに1＝PC、2＝モバイルという風に入っており、1、2の数字は変更できる仕様になっています。
1、2の数字を変更した場合、当然以下のようなプログラムでは正常に動かなくなってしまいます。

DB:
1(変動)=PC(変動)
2(変動)=モバイル(変動)

$typeにDBの1、2が入ってきます。

function getIsMobile($type) {
	$mobile = false;
	switch($type) {
		case 1:
			$mobile = false;
			break;
		case 2:
			$mobile = true;
			break;
		default:
			$mobile = false;
	}
	return $mobile;
}

DB:
1(変動)=PC(固定)=PC(変動)
2(変動)=MOBILE(固定)=モバイル(変動)

$typeにDBのPC(固定)、MOBILE(固定)が入ってきます。

function getIsMobile($type) {
	$mobile = false;
	switch($type) {
		case 'PC':
			$mobile = false;
			break;
		case 'MOBILE':
			$mobile = true;
			break;
		default:
			$mobile = false;
	}
	return $mobile;
}

解決方法としては、1、2の数字が変動しないようにするか、別に固定のIDを振ってあげて、その固定のIDで判別してあげれば、数字が変動しても動作する柔軟なプログラムになると思います。
基本・・ですよね・・（´・(ｪ)・`;）

下音タヌキ PHP, データベース

こんにちわ！かーつんです！！
不定期更新SQLチューニング基礎3発目です。

ん～と、今回は「union (時と場合でminus)」です。

2つのテーブルの集計結果など、同時に取ってきたいとき。
まぁ、方法としては3種類。

１．2回クエリを投げて、PGで加工する。
２．副問い合わせでSQL内で加工する。
３．クエリ的には1回でunionで結果のみがっちゃんこ。

コスト的には、１＜３＜２の順で高くなるって感じでしょうか。
副問い合わせは使い方にも依りますが、mysqlで使うには少々気が咎める時がありますよね。
でも、どうしても2回クエリを投げれない(投げたくない)。ときってあると思うんです。

そんなとき、同じSQLを2回書いて、unionでくっつけて、結果にしてしまう。
こんな事をたまに思いつきます。
ただ、本来のunionの使い方は、結果の合成なので、
同じフィールド名を持つ違うテーブルの値を合算させてしまうのが定石なのですが、
あ　え　て、同じフィールド名をもつ違うテーブルの値に別名つけて別レコードとして、
同時に取得する。

まぁ、どういうことかというと、

SELECT COUNT(hoge) FROM exampleA;
SELECT COUNT(hoge) FROM exampleB;

というのを、

SELECT COUNT(hoge) AS hogeA FROM exampleA;
UNION
SELECT COUNT(hoge) AS hogeB FROM exampleB;

てな具合にかいて、

結果を

hogeA hogeB
100 200

という具合に取得するわけです。
PG内での加工は通常の結果より面倒ですが、
1度に取らなきゃいけないときに、1度で取れるのがメリットです。

なんかまぁ、基礎というより裏技っぽくなっちゃいましたが、
こんな事も出来ますよ。ってくらいで、知ってて損はないはずです。

あと、MySQLでは使えないのですが、
「MINUS」についても同じような使い方をすることが出来るときがあるので、
いろいろ工夫してみてください。

いじょ。かーつんがお送りしました。

かーつん MySQL, データベース

チョコボールです。
あと一か月働けば実務経験1年です。

前回csvファイルの一般的な書式について書きましたが、

その続きとして、DBから引っ張ってきたデータをCSV出力する関数を作ってみました。

例えば

+----+--------+-------+
| id |   name | level |
+----+--------+-------+
|  1 |  quest |    50 |
|  2 | arthur |    45 |
|  3 |  maria |    35 |
+----+--------+-------+

のようなテーブルが有り、これらを全て引っ張ってきてごにょごにょして下記のような配列に格納するとします。

$header(1行目の項目名)
Array
(
	[0] => ID
	[1] => 名前
	[2] => レベル
)

$data(データ部分)
Array
(
	[0] => Array
		(
			[id] => 1
			[name] => quest
			[level] => 50
		)

	[1] => Array
		(
			[id] => 2
			[name] => arthur
			[level] => 45
		)

	[2] => Array
		(
			[id] => 3
			[name] => maria
			[level] => 35
		)
)

これら配列を下記の関数に渡してやります。

downloadCsv($header, $data, 'member');

function downloadCsv($header = array(), $data = array(), $prefix = '')
{
	$ret = '';
	// ヘッダー(1行目の項目名)がある場合
	if(count($header) > 0){
		foreach($header as $val){
			//「"」は「""」に置換してエスケープ
			$tmp[] = str_replace('"', '""', $val);
		}
		$ret .= '"'.implode('","', $tmp).'"'."\r\n";
		unset($tmp);
	}

	foreach($data as $val){
		foreach($val as $vval){
			$tmp[] = str_replace('"', '""', $vval);
		}
		$ret .= '"'.implode('","', $tmp).'"'."\r\n";
		unset($tmp);
	}

	// 文字コードを変換
	$ret = mb_convert_encoding($ret, 'SJIS', 'UTF8');

	// 出力する際のファイル名
	$file_name = $prefix.date('Ymd').'.csv';

	// HTTPヘッダ出力
	Header("Content-Disposition: attachment; filename=${file_name}");
	Header("Content-Type: application/octet-stream; name=${file_name}");
	Header("Cache-Control: ");
	Header("Pragma: ");

	// データを出力
	echo $ret;
	exit;
}

member20081031.csvみたいな感じでDLできると思います。

もちろん、ソース内コメントの「文字コードを変換」部分は内部文字コードに合わせて変更しないとCSVが文字化けしてしまいます。
上記はUTF-8の場合です。

あと、バッファーに何か入ってる場合(デバッグ出力文字等)、その文字もCSVに出力されてしまうので、一番最初の行に

ob_end_clean();

を追加して出力のバッファリングをオフにすると良いです。

一度軽くハマったんですが “\r\n” は ‘\r\n’ じゃ駄目です。
文字列として認識してしまいますので。
ダブルクォートで囲んであげましょう。

チョコボール MySQL, PHP, データベース

こんばんわ。かーつんです。

SQL負荷って、考えたことありますか？

条件などにもよりますが、
SELECT、INSERT、DELETE、UPDATE順に徐々に重くなります。
理由は簡単。

SELECT＝検索
INSERT＝挿入＋インデックスレコード追加
DELETE＝検索＋削除＋インデックスレコード更新
UPDATE＝検索＋更新＋インデックスレコード更新

と、処理が増えるからです。
例えば、UPDATEを１００回投げるなら、
TRUNCATE＋INSERTの方が早い事もあります。
要するに更新が一番重く、検索が一番早いわけですね。

で、よくあると思いますが、
あればUPDATEかけて、なければINSERTというのを
SELECT＋UPDATE＋INSERTの組み合わせでやってる方が多いと思います。
そんなときは、迷わずREPLACEを使いましょう。
REPLACEは検索＋[更新｜挿入]＋インデックス[更新｜挿入]です。

上の例だと、
SELECT＋（UPDATE or INSERT）＝検索＋[（検索＋更新）｜挿入]
REPLACE＝検索＋[更新｜挿入]

どちらが早いか一目瞭然ですよね。
検索一回分REPLACEの方が早いです。
もちろん条件的に無理。ということもありますが、
使えるときは使っちゃった方が、処理が早いです。

プログラムで、
ファイルレコード1件ごとにSELECT投げて、
UPDATEかINSERTか判断して実行して、
これを１０００回繰り返す。コレは悪です。やってたら犯罪です。

一括でファイル読み込んで、
一括でSELECT投げて、
REPLACEで一気に１０００回更新

置き換えれる時は迷わず変えてしまいましょうネ

以上。か＾－＾つんがお送りしました。

かーつん MySQL, データベース

二日連続で失礼します。
チョコボールです。

昨日の天気は荒れに荒れてましたね。。
今も荒れてますが。。
長時間雷鳴りっぱなしの豪雨！
住まいは東京の西の方なのでモロに影響受けました。。

PCが壊れるとまずいので家帰ってからずっとPCの電源抜いてました。
というのも、落雷で家電製品が壊れた…なんて話をよく聞くからです。

一か月ほど前、近所で落雷があり、同じアパートの住人がPCの電源が立ち上がらなくなった…と悔やんでいました。
その時、自分は家にいましたが、危険を感じて電源引っこ抜いていたので運よく無事でした。。

ここまで落雷が多いと、雷サージ対策をしなきゃまずいって気持ちになってきます。
今度電気屋に行った時は絶対買おう。。

昨日から久々にCakePHPを触ることになりました。
弊社コマースシステムの管理機能追加です。

現在のCakePHPのバージョンは1.2ですが、弊社のシステムはバージョン1.1で構築したもの。

ほぼ初心者の頃、いきなりCakePHPを触っていた自分はDB関係のデータ取得等はメソッドを使いまくっていたのでSQLになかなか慣れませんでした。
他人が書いたSQL文中のJOINという文字を見るとどうなってるか分からず、拒絶反応を起こしてしまう…というようなひどい状態が続いていました。
テーブルを結合してデータを取ってきてくれたりするCakePHPのアソシエーション機能は非常に便利ですが、理解しないままこれを利用しているといつまでたってもSQLについて詳しくなれません。。

SQLはできるだけベタに近い状態で書いた方がフレームワークを理解していない人でもすぐ理解でき、保守は楽だと思います。
無駄なSQLをたくさん流すと負荷がかかって遅くなりますし。。
処理が重くなるところはできるだけ簡素で効率のいいSQLを書きたいものです。

一通りWEBアプリケーションに精通している方達はこのようなフレームワークを使った方が高速に開発できると思います。

CakePHPの好きなところはすごく好きなんですけどね。。
システムとして見るとかなり勉強にもなります。

パフォーマンス性や保守性を重視するか、高速開発を重視するか…。

ただ間違いないと思うことは、処理が重いシステムは誰も嫌がる…とういことです。

チョコボール MySQL, PHP, Tips, データベース

はいどうも
のびーにょです

表題のとおりなんですけど、皆さんアクセスログってどう処理してます？

僕の場合はシステム側でDBに取るようにしてます。

オーバーヘッドとか色々考えなきゃだめな部分ありますけど、割と便利です。

まず、Apacheの標準のアクセスログだとPOSTの値がとれません。

※こんな方法もあったりしますが
http://neta.ywcafe.net/000607.html

で、僕はアクセスすべてをDBに保存しています。
レコード数が多くなりすぎたりすると処理に時間がかかってしまいますので月別、週別でアーカイブしていったり、場合によっては日別でテーブルを分けるなどの処理が必要ですが・・・
以下bash

#!/bin/bash
mysql -uユーザ名 -pパスワード rutty_test -hホスト << EOT
ALTER TABLE access_log RENAME AS access_log_`date +%Y_%m_%d`;
CREATE TABLE \\`access_log\\` (
  \\`id\\` int(11) unsigned NOT NULL auto_increment,
  \\`access_date\\` datetime NOT NULL,
  \\`carrier\\` enum('DOCOMO','EZWEB','SOFTBANK','OTHER')NOT NULL default 'OTHER',
  \\`u_id\\` int(11) unsigned default NULL,
  \\`jb\\` varchar(255) NOT NULL,
  \\`parameter\\` longtext,
  PRIMARY KEY  (\\`id\\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EOT

とかのBASHを日別とか週別とか月別とかで実行してやればテーブルのサイズはそんなに大きくなることはないと思います。

フレームワークとか使っているならPOSTとかGETで受け取るものはすべてラッピングできると思うので、そこでDBにほりこんでおけばすべての値を得ることができると思います。

アクセスログをDBに入れておくと何が嬉しいか

集計するとき楽なんですよね。

ちなみに上にあるアクセスログのテーブルのカラムの一つ”jb”ってのは僕の使っているフレームワークののページ名です。
それ集計するだけで日別の各ページのアクセス数がとれるのですごく楽です。

携帯メインなのでキャリアの情報も格納しておけばさらにキャリア別でとれるし。

さっきのBASHを少し変えて、1週間前のテーブルなら削除するとかの処理入れておけばDBもそんなに大きくならなくていいかなーと思います。
集計結果を保存しておくテーブルは当然別につくり、cronとかで日別集計、週別集計、月別集計のバッチ組んでやれば古いデータは必要ないですし、CDなりDVDなりに焼いてやればHDDも圧迫しませんし。
POSTの値も保存できるので作ったアプリケーションの障害時に何が原因かってのも追えますしね。
結構お勧めです。

のびーにょ Apache, MySQL, Tips, データベース

本日2度目の投稿。かーつんです。

先にSQLのチューニング基礎書いたので、
今回はDB設計時の注意点など書いてみようかと思います。

みなさんDB設計するとき何を元に設計されてますか？
基本的に私に限らず画面設計を元にされることが多いのではないでしょうか。

画面にどんな項目があって、その項目にはどんな値が入って。などですね。
よく(私はそうだった)ですが、学校などでDBを教えてくれる所は、
正規化をきちんとするように。と言われるはずです。

まぁ、得られる情報・データを整理して無駄を省き、
整理整頓した形でDBに保存できるように、テーブルを作成しましょう。
という事なのですが、正直、実際の現場で一般的に教わる第三正規形を
キッチリ作り上げているDBは少ないです。

こんな事を書くと、「正規化なんてしなくて良い」と誤解されそうですが、
そういうわけでもないんですね。
現場で行われているのは、

「正規化したデータに冗長性を持たせる」

というものです。
最初からやらないというは、
「散らかるから部屋の掃除をしない」
というのと同じで、ゴミと一緒に必要なものが隠れていてわからなくなることがあります。
やっているのは、
「片付けた後に、必要なものだけを引き出す」
です。

よくあるのは、
正規化した段階でマスタっぽくなっているテーブルに
冗長性を持たせることで、集計時のSQL負荷を軽減させる。とかでしょうか。

つまり。
DB設計時には、テーブル同士の関連性を見極めて、
１テーブル更新することによる他テーブルへの影響を把握しておき、
影響による弊害を極限まで減らすことが重要です。

これがキッチリ行われていると、
テーブルを変更する際も、何も考えられていないときとは比較にならないくらい
作業量に差が出ます。
(テーブル追加なんかは無問題なんですけど、変更・削除はしんどいのです。)

では、今回はここまで。
か－д－つんがお送りしました。

かーつん データベース