SQLにおけるBETWEENの境界値と、カラムの精度に注意

MySQL

疑問

column BETWEEN A AND B」は、以下のどちらと同等か?

  1. A <= column AND column <= B
  2. A <= column AND column < B

A: 1が正しいです

説明

BETWEENの境界値の扱いは、指定した2つの項を含みます

[column] BETWEEN [x] AND [y]

上のクエリーは以下と同等です。

[x] <= [column] AND [column] <= [y]

境界値を含めたくない場合はそれぞれの項で書く必要があります。

[x] < [column] AND [column] = [y]
[x] < [column] AND [column] <= [y]
[x] <= [column] AND [column] < [y]

日付の指定の場合は注意!

BETWEENで対象とするカラムが日付や時間の指定の場合は要注意です。

こちらのURLで指摘されていることになります。以下に要点を書きます。

DATETIME型は、普段目にしているデータはYYYY-MM-DD hh:ii:ssという表記ですが、データとしては秒を小数点6桁まで保持しています。

よって以下のようなデータが保存されます。

  • 2019-01-31 23:59:59.000000
  • 2019-01-31 23:59:59.000001

上記のようなデータが入ることから、境界値を明確に意識したクエリーを書く必要があります。

例えば、月の集計をしたいから以下のようなクエリーを書くケースがあると思いますが、意図しない結果が返却される可能性があります。というか、おそらく意図と結果が異なることになるので間違いといってしまっても良いです。

column BETWEEN '2019-01-01 00:00:00' AND '2019-01-31 23:59:59'
or
'2019-01-01 00:00:00' <= column AND column <= '2019-01-31 23:59:59'

例えば、以下のように、”2019-01-31 23:59:59″ のデータが入っていても集計対象には入りません。

> /* WRONG QUERY */
>  select '2019-01-31 23:59:59.1' between  '2019-01-01 00:00:00' and '2019-01-31 23:59:59' ;
+-----------------------------------------------------------------------------------+
| '2019-01-31 23:59:59.1' between  '2019-01-01 00:00:00' and  '2019-01-31 23:59:59' |
+-----------------------------------------------------------------------------------+
|                                                                                 0 |
+-----------------------------------------------------------------------------------+
1 row in set (0.004 sec)

以下のように書くのが安全です。

> set @columnA='2019-02-01 00:00:00';
Query OK, 0 rows affected (0.002 sec)

> select '2019-01-01 00:00:00' <= @columnA AND @columnA < '2019-02-01 00:00:00';
+-------------------------------------------------------------------------+
| '2019-01-01 00:00:00' <= @columnA AND @columnA <= '2019-02-01 00:00:00' |
+-------------------------------------------------------------------------+
|                                                                       0 |
+-------------------------------------------------------------------------+
1 row in set (0.007 sec)

以下に関連するMySQLのマニュアルを引用しておきます。

DATETIME[(fsp)]
日付と時間の組み合わせです。サポートしている範囲は '1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999' です。MySQL は、'YYYY-MM-DD HH:MM:SS[.fraction]' の形式で DATETIME 値を表示しますが、文字列または数値のどちらかを使用した DATETIME カラムへの値の割り当てを許可しています。
MySQL 5.6.4 以降では、小数秒の精度を指定するために 0 から 6 の範囲でオプションの fsp 値を指定できます。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。
MySQL 5.6.5 以降、DATETIME カラムに対する自動初期化および現在の日時への自動更新は、セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」で説明しているように、DEFAULT および ON UPDATE カラム定義句を使用して指定できます。

https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-type-overview.html

MySQLにおけるdatetimeはオプションによって秒より高い精度で保存されている可能性があるので、このクエリーでは意図しない結果が返ってくる可能性がある。論理的に正しいコードを書いていれば問題無いけど。

コードレビューの時に見逃しそうで怖いので、ユニットテストで担保するのが良さそうです。

世の中の人はどうしているのか気になってグーグルしてみたら、MySQLだけで6万件ぐらい怪しい記述が出てきました。なぜ怪しいかというと、BETWEEN句と23:59:59を使うと境界値がおそらく誤って使われているからです。

言い換えると、プログラムのソースコードを全文検索して、23:59:59という表記があったら要注意です。

まとめ

  • 境界値の表現方法と、カラムの精度をちゃんと理解して実装する必要がある。
  • 物理的には正しいが、論理的には間違った実装をしてしまって不正確な集計を行ってしまう場合が出てくる。
MySQL徹底入門 第4版 MySQL 8.0対応
Amazon.co.jp: MySQL徹底入門 第4版 MySQL 8.0対応 eBook : yoku0825, 坂井 恵, 鶴長 鎮一, とみた まさひろ, 深町 日出海, 福山 裕大, 班石 悦夫, 山﨑 由章: 本

コメント

タイトルとURLをコピーしました