この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。
MySQL DATETIME型のミリ秒の扱いについて
MySQLのDATETIME型のカラムに対して、ミリ秒付きデータを書き込む際の動作について、
バージョンによって挙動が異なるので注意が必要です。
MySQL 5.5 までは、強制的に切り捨てされて格納、MySQL 5.6 以降は四捨五入されて格納されるという動作になっています。
実行例
■ [MySQL 5.5]
テーブル
mysql> show columns from hoges;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | varchar(255) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
書き込むデータ
mysql> SET @time1 = "2016-04-28 12:00:00.100";
mysql> SET @time2 = "2016-04-28 12:00:00.499";
mysql> SET @time3 = "2016-04-28 12:00:00.500";
mysql> SELECT @time1, @time2, @time3;
+-------------------------+-------------------------+-------------------------+
| @time1 | @time2 | @time3 |
+-------------------------+-------------------------+-------------------------+
| 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00.500 |
+-------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)
データ格納結果
mysql> INSERT INTO hoges (data, time) VALUES (@time1, @time1);
mysql> INSERT INTO hoges (data, time) VALUES (@time2, @time2);
mysql> INSERT INTO hoges (data, time) VALUES (@time3, @time3);
mysql> select * from hoges;
+----+-------------------------+---------------------+
| id | data | time |
+----+-------------------------+---------------------+
| 3 | 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00 |
| 4 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00 |
| 5 | 2016-04-28 12:00:00.500 | 2016-04-28 12:00:00 |
+----+-------------------------+---------------------+
■ [MySQL 5.7]
テーブル
mysql> show columns from hoge;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | MUL | NULL | auto_increment |
| data | varchar(255) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
書き込むデータ
mysql> SET @time1 = "2016-04-28 12:00:00.100";
mysql> SET @time2 = "2016-04-28 12:00:00.499";
mysql> SET @time3 = "2016-04-28 12:00:00.500";
mysql> SELECT @time1, @time2, @time3;
+-------------------------+-------------------------+-------------------------+
| @time1 | @time2 | @time3 |
+-------------------------+-------------------------+-------------------------+
| 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00.500 |
+-------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)
データ格納結果
mysql> INSERT INTO hoge (data, time) VALUES (@time1, @time1);
mysql> INSERT INTO hoge (data, time) VALUES (@time2, @time2);
mysql> INSERT INTO hoge (data, time) VALUES (@time3, @time3);
mysql> select * from hoge;
+----+-------------------------+---------------------+
| id | data | time |
+----+-------------------------+---------------------+
| 1 | 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00 |
| 2 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00 |
| 3 | 2016-04-28 12:00:00.500 | 2016-04-28 12:00:01 |
+----+-------------------------+---------------------+
3 rows in set (0.00 sec)
上記のように、同じデータを書き込んだ場合でも、結果が異なったものになっている。
■ おまけ
Railsでの動作について
RailsのActiveRecord経由で記録した場合、
Rails ~4.1だと、ミリ秒は切り捨てされて、
Rails 4.2~ だと、ミリ秒も含めてSQL文が作られる。
