mysqlコマンドとPHPで同じSQLの結果が違う

質問

MySQL 5.5で、mysqlコマンドからSQLを流したときと、PHPから同じSQLを発行したときで結果が違うのです。
どうも日付のパースが関係しているようで、「2014-08-19」と書くとどちらも同じ結果になるのに、
「2014-8-19」と書くとPHPでは1行も返ってこなくなります。

再現例：
==================
CREATE TABLE t (day date NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO t (day) VALUES ('2014-08-19');

SELECT * FROM t WHERE day = '2014-8-19';
+------------+
| day |
+------------+
| 2014-08-19 |
+------------+
1 row in set (0.00 sec)

↑同じSELECTをPHPから発行すると1行も返ってこない。
==================

mysqldとmysqlは5.5.30。
phpinfo()のmysqlのセクションでは
Client API version 5.5.30
と出ています。

show variablesで見るとdate_formatはどちらも%Y-%m-%dになっています。

他にどこを確認すればいいでしょうか？

回答No.3

mpro-gram

cast してみたら？ insert のときは、カラム型に合わせて、かなり柔軟対処してくれるけど
where などでの比較式においては、カラム値の方を文字列変換してから、文字列比較したりすることがあるらしいので（特にdatetime と日付だけの文字列を比較したとき）、今回は文字コードかCOLLATE の関係で、それが発生していると見られます。
その場合、文字列を日付にキャストするといいらしいです。

WHERE day = CAST('2014-8-19' AS DATE)

　この日付文字列の所を、プレースホルダーにすれば、ユーザーから日付入力して貰っても、範囲外でもない限りかなり柔軟に、日付対応できます。
参考
http://matsu.teraren.com/blog/2010/07/27/mysql-date-string/
「MySQLの日付比較：比較対象のカラムは型を意識しなければならない。」

回答No.2

yambejp

キャラクターコードの問題かどうかはちょっとわからないですが
それで解決できるならいずれにしろキャラクターコードは指定しなくてはいけない
のですから、それで対応するのもありかもしれません
もちろん、SQLの日付の標準形式（YYYY-MM-DD）で書くのが原則ですが。

MySQLの場合、日付の完全チェックがバージョンによって微妙に違うみたいなので
場合によっては、SELECT文を発行する前にSQLモードを明示してみるとよいかも

SET SQL_MODE='ALLOW_INVALID_DATES';
SELECT * FROM t WHERE day = '2014-8-19';

なおmy.cnfやmy.iniなどでグローバルなSQLモード指定もできるようです。

http://dev.mysql.com/doc/refman/5.1/ja/server-sql-mode.html#idm47509643352352

お礼

回答ありがとうございます。

アプリがsjisで書かれていてキャラクターコードはsjisでなければいけないので、対応にはならないのです。

SET SQL_MODE='ALLOW_INVALID_DATES';
を発行してもSQLの結果は変わりませんでした。

今のところ、対応方法はYYYY-MM-DDを使うようにアプリを変更するしかない状況です。

回答No.1

yambejp

＞同じSELECTをPHPから発行すると1行も返ってこない。

PHPからどうやって発行しているのでしょうか？
mysql関数、mysqli関数、PDO？
またプリペアドな処理はしていますか？

お礼

PHPで再現させる最小のコードです。

$link = mysqli_connect('localhost', '*********', '********', '**********');

// ↓sjisとutf8を切り替えると結果が変わる
mysqli_set_charset($link, "sjis");
//mysqli_set_charset($link, "utf8");

$sql = "SELECT * FROM t WHERE day = '2014-8-19'";
$res = mysqli_query($link, $sql);

echo $res->num_rows, "\n";

while ($row = $res->fetch_row()) {
var_dump($row);
}

mysqli_close($link);

補足

ありがとうございます。

PEAR::DB 1.7.13でmysqliを使っています。

PEAR::DBのmysqli.php 400行目でmysqli_queryを呼び出していて、そこで渡しているSQLが変更されていないことを確認しましたし、そのコネクションを使って自分でmysqli_queryを呼ぶとやはり同じ結果になります。

さらに調べたところ、
set names sjis;
を呼ぶと、コマンドラインmysqlでもSQLの結果が変わることが判明しました。