PakuReserve(パクレゼルヴ) R&D Laboratory

チョコボールです。
MySQLにてSELECT時に値の形を変えて引っ張ってくる方法。
僕はステータスが数字で管理されているようなDB(※例: 男性が「1」、女性が「2」等)をfetchしてきて表示する時等に使ってます。

取り敢えずSELECTで引っ張ってきてプログラム側で加工してやればいいんですが、CASE式を使えば加工の必要が無いので結構楽です。

下記のようなmemberテーブルがあったとします。

+----+------+--------+
| id | name | status |
+----+------+--------+
|  1 | 田中 |      0 |
|  2 | 辻川 |      1 |
|  3 | 鈴木 |      0 |
|  4 | 山本 |      1 |
|  5 | 西田 |      0 |
|  6 | 上村 |      2 |
|  7 | 星野 |      0 |
|  8 | 宮村 |      3 |
|  9 | 川田 |      1 |
| 10 | 山田 |      1 |
+----+------+--------+

ステータス
0 → 通常会員
1 → 有料会員
2 → 退会済会員

そのまま

SELECT * FROM member

としてfetchすると

Arrray
(
	[0] => Array
		(
			[id] => 1
			[name] => 田中
			[type] => 0
		)

	[1] => Array
		(
			[id] => 2
			[name] => 辻川
			[type] => 1
		)

	[2] => Array
		(
			[id] => 3
			[name] => 鈴木
			[type] => 0
		)
			・
			・
			・

のような感じで取得できますが、下記のようなSQLを組むことでプログラム側で加工無しに一発で引っ張ってくることが出来ます。

SELECT id, name,
	CASE
		WHEN type = '0' THEN '通常会員'
		WHEN type = '1' THEN '有料会員'
		WHEN type = '2' THEN '退会済会員'
		ELSE '不明'
	END type
FROM member

 Array
(
	[0] => Array
		(
			[id] => 1
			[name] => 田中
			[type] => 通常会員
		)

	[1] => Array
		(
			[id] => 2
			[name] => 辻川
			[type] => 有料会員
		)

	[2] => Array
		(
			[id] => 3
			[name] => 鈴木
			[type] => 通常会員
		)
			・
			・
			・

CASE式は他にもいろいろ使えるのでまた紹介します！

MoriMoriMoriMori MySQL, 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’ じゃ駄目です。
文字列として認識してしまいますので。
ダブルクォートで囲んであげましょう。

MoriMoriMoriMori MySQL, PHP, データベース

mysqlにはmysqldumpとかいう便利なバックアップ機能がついています。

mysqldump --all-databases -uユーザー名 -pパスワード DB名 > 保存ファイル名.sql

とすると、そのサーバーのDB全てが保存ファイル名として保存されます。
--all-databasesと指定すると、DB、テーブル、レコード全てが出力されます。
ここのパラメーターを変更することで、テーブルのみとかレコードのみとか指定できます。

よく使うパラメータは以下のとおり。

-A、--all-databases
	全てのデータベースを指定。
-B、--databases
	出力するデータベースを指定する。
-t、--no-create-info
	CREATE TABLE情報を残さない。
-d、--no-data
	レコードを残さない。

こんな感じで、手軽にバックアップができます。
で、戻す方法は・・

mysql -uユーザー名 -pパスワード DB名 < 保存ファイル名.sql

出力の時に使った「>」を「< 」とするだけで、入力できるお手軽さ。

ちなみに、mysqldumpのデフォルト文字コードはUTF-8のようで、環境によっては文字化けてしまう場合があるので、出力の際に「--default-character-set=文字コード」パラメータを追加することで、指定の文字コードに出来るようです。
そんな感じ。