2011年04月04日

MySQL 5.5 ではDATE型とDATETIME型の比較結果が異なる

 端的に言うと、今までは日の00:00:00を指したDATETIME値と同日のDATE値は等しかったが、5.5.x では異なる。
 今までのMySQLでは、
mysql> SELECT CAST( DATE(SYSDATE()) AS DATETIME ) = CAST( DATE(SYSDATE()) AS DATE )
AS datecomp;
+----------+
| datecomp |
+----------+
| 1 |
+----------+
 結果は真だが、MySQL 5.5.10 では、
mysql> SELECT CAST( DATE(SYSDATE()) AS DATETIME ) = CAST( DATE(SYSDATE()) AS DATE )
AS datecomp;
+----------+
| datecomp |
+----------+
| 0 |
+----------+
 同じSQL文でも結果は偽である。

 これは、DATE型とDATETIME型を比較するときの暗黙の型変換に仕様変更があったということか。
 以前のMySQLでは、DATE型と、同日の'00:00:00'の時刻を持つDATETIME型は同じ値だった(DATE型がDATETIME型に暗黙に変換される時、'00:00:00'を加えられていた)が、5.5.xでは、違う値として処理される。
 次のようなSQLを実行してみると、
5.5.10
mysql> SELECT CAST( DATE(SYSDATE()) AS DATETIME ) > CAST( DATE(SYSDATE()) AS DATE )
AS test;
+------+
| test |
+------+
| 1 |
+------+

5.0.77
mysql> SELECT CAST( DATE(SYSDATE()) AS DATETIME ) > CAST( DATE(SYSDATE()) AS DATE )
AS test;
+------+
| test |
+------+
| 0 |
+------+
 となるので、DATE型がDATETIME型に暗黙に変換される場合、'00:00:00' より小さい時刻の値で埋められるように変更されたのではないだろうか(ドキュメントは軽くしか読んでないけどどっかに書いてあるかも)。

 DATE() 関数で日付の切り捨てをしていたりするプログラムは、注意を要する。
 今までと同じ結果を得るためには、CAST() で明示的にDATETIME型に変換してやる必要があるようである。
CREATE TABLE datetest (
idnum INTEGER,
tdate DATETIME,
CONSTRAINT pk_datetest PRIMARY KEY (idnum)
);
INSERT INTO datetest (idnum,tdate)
VALUES(1,STR_TO_DATE('2011/04/04','%Y/%m/%d'));
INSERT INTO datetest (idnum,tdate)
VALUES(2,STR_TO_DATE('2011/04/04 12:00:00','%Y/%m/%d %H:%i:%s'));

-- 5.0.77の場合 SYSDATEは11/04/04 07:00:00
mysql> SELECT * FROM datetest WHERE tdate <= DATE(SYSDATE());
+-------+---------------------+
| idnum | tdate |
+-------+---------------------+
| 1 | 2011-04-04 00:00:00 |
+-------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM datetest WHERE tdate <= CAST( DATE(SYSDATE()) AS DATETIME );
+-------+---------------------+
| idnum | tdate |
+-------+---------------------+
| 1 | 2011-04-04 00:00:00 |
+-------+---------------------+
1 row in set (0.00 sec)

-- 5.5.10の場合 SYSDATEは11/04/04 07:00:00
mysql> SELECT * FROM datetest WHERE tdate <= DATE(SYSDATE()); -- 5.0.77と違う結果が返される
Empty set (0.00 sec)

mysql> SELECT * FROM datetest WHERE tdate <= CAST( DATE(SYSDATE()) AS DATETIME );
+-------+---------------------+
| idnum | tdate |
+-------+---------------------+
| 1 | 2011-04-04 00:00:00 |
+-------+---------------------+
1 row in set (0.00 sec)


タグ:Linux MySQL
posted by usoinfo at 07:31 | Comment(1) | 開発 | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
これは、DATE型とDATETIME型を比較する際に、型が異なるので、文字列に変換された上で比較されるようになったのではないでしょうか。あくまでも推測ですが。
Posted by hoge at 2013年09月19日 20:56
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

×

この広告は180日以上新しい記事の投稿がないブログに表示されております。