※こちらは旧サイトです(新サイトはこちら

MySQLの比較演算子が意外とゆるかった件

2017-09-19 16:59:14

MySQL > select * from luxes where id = '657';
+-----+---------------------+------+---------------------+---------------------+
| id  | recorded_at         | lux  | created_at          | updated_at          |
+-----+---------------------+------+---------------------+---------------------+
| 657 | 2016-11-27 15:44:34 | 1530 | 2016-11-27 15:44:34 | 2016-11-27 15:44:34 |
+-----+---------------------+------+---------------------+---------------------+
1 row in set (0.00 sec)

MySQL > select * from luxes where id = '657      ';
+-----+---------------------+------+---------------------+---------------------+
| id  | recorded_at         | lux  | created_at          | updated_at          |
+-----+---------------------+------+---------------------+---------------------+
| 657 | 2016-11-27 15:44:34 | 1530 | 2016-11-27 15:44:34 | 2016-11-27 15:44:34 |
+-----+---------------------+------+---------------------+---------------------+
1 row in set (0.00 sec)

MySQL > select * from luxes where id = '657      abc';
+-----+---------------------+------+---------------------+---------------------+
| id  | recorded_at         | lux  | created_at          | updated_at          |
+-----+---------------------+------+---------------------+---------------------+
| 657 | 2016-11-27 15:44:34 | 1530 | 2016-11-27 15:44:34 | 2016-11-27 15:44:34 |
+-----+---------------------+------+---------------------+---------------------+
1 row in set, 1 warning (0.00 sec)

MySQL > select * from luxes where created_at = '2016-11-27 15:44:34';
+-----+---------------------+------+---------------------+---------------------+
| id  | recorded_at         | lux  | created_at          | updated_at          |
+-----+---------------------+------+---------------------+---------------------+
| 657 | 2016-11-27 15:44:34 | 1530 | 2016-11-27 15:44:34 | 2016-11-27 15:44:34 |
+-----+---------------------+------+---------------------+---------------------+
1 row in set (0.01 sec)

MySQL > select * from luxes where created_at = '2016-11-27';
Empty set (0.00 sec)

MySQL > select * from luxes where created_at = '2016-11-27        ';
Empty set (0.00 sec)

普段あんまりこういう事しないから気付いてなかったけど、Datetime型が時間の部分切り捨てられてない事から、やっぱInt型カラムに対してやった場合はこういう仕様なんかなー

と、とあるバグ対応を見て思ったので忘れないようメモ

MySQL 5.6 リファレンスマニュアル 12.3.2 比較関数と演算子