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

こんばんは。MoriMoriMoriMoriです。

今回は、なんとなくうまく行きそうだからOKでしょ、
という感じでやると失敗するパターンについて書きます。

下記のような顧客情報テーブルがあったとします。

DESC users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | NO   |     |         |                |
| birthday | date         | NO   |     |         |                |
| created  | datetime     | NO   |     |         |                |
+----------+--------------+------+-----+---------+----------------+

SELECT * FROM users;
+----+---------+------------+---------------------+
| id | name    | birthday   | created             |
+----+---------+------------+---------------------+
|  1 | takeshi | 1986-01-21 | 2010-02-10 11:40:30 |
|  2 | isamu   | 1980-01-05 | 2010-05-17 15:12:32 |
|  3 | mikiko  | 1983-12-12 | 2010-05-31 18:10:18 |
|  4 | fumio   | 1976-07-13 | 2010-06-05 09:37:49 |
+----+---------+------------+---------------------+

データが登録された日付(created)で
2010/1/1～2010/5/31までのデータを抽出したい、とします。

以下のSQLを投げてみましょう。

SELECT * FROM users WHERE created >= '2010-01-01' AND created <= '2010-05-31';

クエリの結果は以下。

+----+---------+------------+---------------------+
| id | name    | birthday   | created             |
+----+---------+------------+---------------------+
|  1 | takeshi | 1986-01-21 | 2010-02-10 11:40:30 |
|  2 | isamu   | 1980-01-05 | 2010-05-17 15:12:32 |
+----+---------+------------+---------------------+

そうです。
created <= '2010-05-31'
としてるにも関わらず、mikikoさんのデータが引っ張って来れてません。

datetime型にdate型で検索しようとすると、予想通りの結果になりません。
面倒くさがらず、以下のようなSQLにしましょう。

SELECT * FROM users WHERE created >= '2010-01-01 00:00:00' AND created <= '2010-05-31 23:59:59';

または

SELECT * FROM users WHERE created >= '2010-01-01 00:00:00' AND created < '2010-06-01 00:00:00';

MoriMoriMoriMori MySQL

お久しぶりです。

以前、ボトルネックになっているSQLを探していたら以下のような事がありました。

EXPLAIN SELECT * FROM user_master
	WHERE user_id IN (
		SELECT user_id FROM game_user_status);

とすると

+----+--------------------+------------------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table            | type            | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+------------------+-----------------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | user_master      | ALL             | NULL          | NULL    | NULL    | NULL |   86 | Using where |
|  2 | DEPENDENT SUBQUERY | game_user_status | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |
+----+--------------------+------------------+-----------------+---------------+---------+---------+------+------+-------------+

86*1　
で普通に意図した結果が取得できます。

しかし、サブクエリ中でGROUP BYすると酷いことになりました。

EXPLAIN SELECT * FROM user_master
	WHERE user_id IN (
		SELECT user_id FROM game_user_status GROUP BY user_id);

+----+--------------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | user_master      | ALL   | NULL          | NULL    | NULL    | NULL |   86 | Using where |
|  2 | DEPENDENT SUBQUERY | game_user_status | index | NULL          | PRIMARY | 4       | NULL |   67 | Using index |
+----+--------------------+------------------+-------+---------------+---------+---------+------+------+-------------+

GROUP BYすると

86*67

と、検索ロジックに大きな違いが出てきます。

で、原因は”IN”句

MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。これがまさにMySQLのサブクエリが遅い！と言われている原因だろう。
なぜMySQLのサブクエリは遅いのか。

だからってGROUP BY使っただけでそこまでしなくても・・・

と、いう事で以下のように書き換えると解決できるようです。

EXPLAIN SELECT * FROM user_master um
WHERE EXISTS
(SELECT 1 FROM game_user_status gus WHERE um.user_id = gus.user_id);

+----+--------------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                   | rows | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+
|  1 | PRIMARY            | um    | ALL    | NULL          | NULL    | NULL    | NULL                  |   86 | Using where |
|  2 | DEPENDENT SUBQUERY | gus   | eq_ref | PRIMARY       | PRIMARY | 4       | warui_test.um.user_id |    1 | Using index |
+----+--------------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+

個人のブログの方でご指摘いただきました内容を解答例とさせていただいています。
安直にINを利用するのは良くないという例でした。

ちなみにgroup by してもしなくても取得結果が変わらない場合もあります。
今回の例なんかはgroup by しなくても取得結果は変わりません。

むやみやたらと色々つければいいって物でも無い　いい例です。

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

久しぶりの投稿です。
HNはチョコボールでしたがMoriMoriMoriMoriに変えます。
無意味にCamelCaseで。

管理画面でdivブロックのタイトル背景色なんかを設定する項目で、
入力項目に正しいカラーネーム、カラーコードが入力されているかどうかをチェックする
バリデーションの関数を紹介します。

// 有効なカラーネーム(147色)もしくはカラーコードかどうかチェック
function checkColor($str, $name)
{
	if($str == ''){
		return '';
	}
	// カラーネーム配列(147色)
	$arrColorName = array('aliceblue','antiquewhite','aqua','aquamarine','azure','beige','bisque','black','blanchedalmond','blue','blueviolet','brass','brown','burlywood','cadetblue','chartreuse','chocolate','coolcopper','copper','coral','cornflower','cornflowerblue','cornsilk','crimson','cyan','darkblue','darkbrown','darkcyan','darkgoldenrod','darkgray','darkgreen','darkkhaki','darkmagenta','darkolivegreen','darkorange','darkorchid','darkred','darksalmon','darkseagreen','darkslateblue','darkslategray','darkturquoise','darkviolet','deeppink','deepskyblue','dimgray','dodgerblue','feldsper','firebrick','floralwhite','forestgreen','fuchsia','gainsboro','ghostwhite','gold','goldenrod','gray','green','greenyellow','honeydew','hotpink','indianred','indigo','ivory','khaki','lavender','lavenderblush','lawngreen','lemonchiffon','lightblue','lightcoral','lightcyan','lightgoldenrodyellow','lightgreen','lightgrey','lightpink','lightsalmon','lightseagreen','lightskyblue','lightslategray','lightsteelblue','lightyellow','lime','limegreen','linen','magenta','maroon','mediumaquamarine','mediumblue','mediumorchid','mediumpurple','mediumseagreen','mediumslateblue','mediumspringgreen','mediumturquoise','mediumvioletred','midnightblue','mintcream','mistyrose','moccasin','navajowhite','navy','oldlace','olive','olivedrab','orange','orangered','orchid','palegoldenrod','palegreen','paleturquoise','palevioletred','papayawhip','peachpuff','peru','pink','plum','powderblue','purple','red','richblue','rosybrown','royalblue','saddlebrown','salmon','sandybrown','seagreen','seashell','sienna','silver','skyblue','slateblue','slategray','snow','springgreen','steelblue','tan','teal','thistle','tomato','turquoise','violet','wheat','white','whitesmoke','yellow','yellowgreen');

	// 精査用に小文字に統一
	$str = strtolower($str);
	if(!preg_match("/^[0-9a-f]{6}$/",$str) && !preg_match("/^[0-9a-f]{3}$/",$str) && !in_array($str, $arrColorName)){
		return $name.'のカラー指定が不正です。<br />';
	}
	return '';
}

データベースへの登録の際、カラムには、カラーネームの場合はそのまま入れ、
カラーコードの場合は、「#ffcc33」のように先頭に#を付けて登録してあげたほうが良いかと思います。
なぜなら、出力の度にカラーコードの場合は#を付ける、
といった判定をする処理を入れるのはパフォーマンスが悪いからです。
微々たる差異ですが。

出力の際のコーディング(ベタ書き)です。

if($title['bgcolor'] == ''){
	echo '＜div style="text-align:center;">'
}else{
	echo '＜div style="text-align:center;background-color:'.$title['bgcolor'].';">';
}
echo 'ブロックタイトル';

XHTMLでは16進数のカラーコードの英字は小文字で指定した方が良いので、
XHTML前提で利用する場合、「FFCCCC」のように入力された場合は強制的に
「#ffcccc」のように小文字にして(#もつけて)登録してあげてもいいかもしれません。
また、入力欄にカラーコードの場合、#つきで入力されても通るようにしてあげるとさらに親切かと。

fontタグのcolor属性の値やbodyタグのtext属性の値等は、
3桁の16進数での表記は当然ダメですよ！

■ダメな例

＜body text="#000">
＜font color="#fff">

ついでに書きますが、XHTMLでの属性checkedの誤った書き方。

<input type="radio" checked />

属性=”属性値”という書き方が基本なので、

<input type="radio" checked="checked" />

と書きましょう！

以上、MoriMoriMoriMoriMoriMoriでした。

MoriMoriMoriMori HTMLとか, MySQL, PHP, データベース

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を返します。

MoriMoriMoriMori 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'));

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

MoriMoriMoriMori MySQL, PHP, Tips, データベース