MySQL5.1でDATETIME列にインデックスを作成した場合に範囲条件の結果が不一致

MySQL

事象

MySQL5.1でDATETIME列にインデックスを作成したら、範囲条件が正しく動作しなくなった。
ちょっと複雑なので順を追って説明します。


1.下記のようなテーブルを作成した。

exampleテーブル

列名 データ型 制約など
id INT PRIMARY KEY,AUTO_INCREMENT
name VARCHAR(50)
update DATETIME NOT NULL

データ

id name update
1 いち 2010-03-09 23:59:59
2 に 2010-03-10 00:00:00
3 さん 2010-03-10 23:59:59
4 し 2010-03-11 00:00:00


2. update列にインデックスがない状態で、update列を範囲条件で絞り込む。以下のSQLを実行。

SELECT id,name,update FROM example WHERE update >= '2010-03-10 00:00:00.0'

※java.sql.Timestampとのマッピング上、後ろに '.0' をつけてます。なお、mysqlコマンドで確認しました。

結果:OK 3件

id name update
2 に 2010-03-10 00:00:00
3 さん 2010-03-10 23:59:59
4 し 2010-03-11 00:00:00


3. update列にインデックスを作成


4. 再度、2.のSQLを実行する。

SELECT id,name,update FROM example WHERE update >= '2010-03-10 00:00:00.0'

結果:NG 2件
id:2の行が取得できていない。

id name update
3 さん 2010-03-10 23:59:59
4 し 2010-03-11 00:00:00


5. 2.のSQLで、出力列をupdate列のみに絞り込んで実行すると成功。

SELECT update FROM example WHERE update >= '2010-03-10 00:00:00.0'

結果:OK 3件

update
2010-03-10 00:00:00
2010-03-10 23:59:59
2010-03-11 00:00:00


6. 2.のSQLで、update列の範囲指定から'.0'を除去すると成功。

SELECT id,name,update FROM example WHERE update >= '2010-03-10 00:00:00'

結果:OK 3件

id name update
2 に 2010-03-10 00:00:00
3 さん 2010-03-10 23:59:59
4 し 2010-03-11 00:00:00

いろいろ調べてみた。

  • 範囲条件でも、以上(update >= '2010-03-12 00:00:00.0')の場合のみ問題が発生する。
  • 以下(update <= '2010-03-12 00:00:00.0')の場合は正しく動作する。

対処

STR_TO_DATEを使用することで解決。
update >= STR_TO_DATE('2010-03-12 00:00:00.0', '%Y-%m-%d %H:%i:%s.0')で正しく動作した。