MySQL5.6以降では時刻系の型に精度未満の値を入れると四捨五入される

昔の記憶のまま使ってたらハマった話。

先に検証用のテーブルを作る。

time, datetime, timestampそれぞれの型で、デフォルト精度（カッコなし）とマイクロ秒精度（6指定）のカラムを加えておきます。

mysql> set sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test(
    ->   id int unsigned not null auto_increment,
    ->   test_time time,
    ->   test_datetime datetime,
    ->   test_timestamp timestamp,
    ->   test_time_full time(6),
    ->   test_datetime_full datetime(6),
    ->   test_timestamp_full timestamp(6),
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.04 sec)

set sql_mode='';は試したCloudSQL環境でエラーになったので足していますが、他の環境では不要かも。

このテーブルにデータを入れて確認してみます。

mysql> insert into test (
    ->     test_time, test_datetime, test_timestamp,
    ->     test_time_full, test_datetime_full, test_timestamp_full
    -> )
    -> values (
    ->     '23:59:59.999999', '2019/12/31 23:59:59.999999', '2019/12/31 23:59:59.999999',
    ->     '23:59:59.999999', '2019/12/31 23:59:59.999999', '2019/12/31 23:59:59.999999'
    -> );
Query OK, 1 row affected (0.02 sec)
mysql> select * from test \G
*************************** 1. row ***************************
                 id: 1
          test_time: 24:00:00
      test_datetime: 2020-01-01 00:00:00
     test_timestamp: 2020-01-01 00:00:00
     test_time_full: 23:59:59.999999
 test_datetime_full: 2019-12-31 23:59:59.999999
test_timestamp_full: 2019-12-31 23:59:59.999999
1 row in set (0.00 sec)

結果は23:59:59となる（秒未満は切り捨てられる）と思っていましたが、そうではなく繰り上げられて24:00:00になっています。さらに日付付きだと翌年になっています。

これは予想外。なぜだろうか。

MySQLはたまにしか使わないけど、昔の記憶を辿ってもこんな処理にはなってなかったと思う。

検索するとMySQL5.5と5.6で挙動が変わると書いてあった。

公式ドキュメントも確認します。

MySQL 5.6.4 より前では、時間値で小数秒部分が許可されているインスタンスは制限されています。（略）ただし、MySQL は時間データ型のカラムに値を格納するときに、小数部を破棄し、それを格納しません。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.6 時間値での小数秒

MySQL 5.6.3までは、破棄、つまり切り捨てが行われるようです。

MySQL 5.6.4 以降では、マイクロ秒 (6 桁) までの精度を持つ TIME、DATETIME、および TIMESTAMP 値に対して小数秒のサポートを拡張しています。

小数秒部分を持つ TIME、DATE、または TIMESTAMP 値を同じ型のカラムに挿入するが、小数部の桁数が少ない場合、次の例に示すように丸めが行われます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.6 時間値での小数秒

MySQL 5.6.4以降は、丸め、つまり四捨五入が行われるようです。

MySQL 5.6系のGeneral Availability（正式）版は5.6.10からなので、実質的に5.5系は切り捨て、5.6系は四捨五入という理解で良さそうです。

さらに、

0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。(これは、以前の MySQL バージョンと互換性を保つため、標準 SQL のデフォルトである 6 とは異なっています。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.20.4 丸め動作

という記述もありました。

標準とは異なると書いてある。なるほど、地雷を踏み抜いてたわ。

トラブルが起きたコード

もともと、精度指定をしないtimestamp型にある期間の終了日時を入れるテーブルがあった。

Python実装だったので以下のようなコード。

>>> import arrow
>>> arrow.get().ceil('day').datetime
datetime.datetime(2019, 7, 4, 23, 59, 59, 999999, tzinfo=tzutc())

終了時間を雑にceilで上げたものをSQLで入れたら、翌日00:00:00にされていた。

で、別の場所では日にちだけの精度でwhereで絞ってたので翌日のデータが混じるというのが起きたトラブルでした。

データの修正

テーブル定義を直したら、すでに入ってる間違ったデータもなんとかしたい。

mysql> select addtime('2020-01-01 00:00:00', '-.000001');
+--------------------------------------------+
| addtime('2020-01-01 00:00:00', '-.000001') |
+--------------------------------------------+
| 2019-12-31 23:59:59.999999                 |
+--------------------------------------------+
1 row in set (0.02 sec)

addtimeで-1マイクロ秒を足してあげればよい。

一括で更新する場合には、whereにmicrosecond()を使うと、ずれたデータだけ指定できる。今回の場合では= 0で比較しておけばよい。

mysql> select microsecond('2020-01-01 00:00:00');
+------------------------------------+
| microsecond('2020-01-01 00:00:00') |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select microsecond('2019-12-31 23:59:59.999999');
+-------------------------------------------+
| microsecond('2019-12-31 23:59:59.999999') |
+-------------------------------------------+
|                                    999999 |
+-------------------------------------------+
1 row in set (0.00 sec)

CloudSQLでcreate tableが失敗する理由

ERROR 1067 (42000): Invalid default value for 'test_timestamp_full'

このエラーが出ます。

原因はsql_modeにNO_ZERO_DATEが指定されているせいです。

MySQL 5.6.17以降ではNO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZEROは非推奨になっています。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

ですが、Cloud SQLは5.6.17以降でもこれらが有効になっているようです。

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.7.14-google-log |
+-------------------+
1 row in set (0.04 sec)
mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

さらに、timestampが2つ以上あって、両方ともdefault nullの場合に失敗します。

なので、以下のSQLのようにdefault値を設定すればエラーが解消されます。

mysql> create table test(
    ->   id int unsigned not null auto_increment,
    ->   test_time time,
    ->   test_datetime datetime,
    ->   test_timestamp timestamp,
    ->   test_time_full time(6),
    ->   test_datetime_full datetime(6),
    ->   test_timestamp_full timestamp(6) default current_timestamp(6),
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

しかし、以下のSQLは失敗します。

mysql> create table test(
    ->   id int unsigned not null auto_increment,
    ->   test_time time,
    ->   test_datetime datetime,
    ->   test_timestamp timestamp default current_timestamp,
    ->   test_time_full time(6),
    ->   test_datetime_full datetime(6),
    ->   test_timestamp_full timestamp(6),
    ->   primary key (id)
    -> );
ERROR 1067 (42000): Invalid default value for 'test_timestamp_full'

この理由はよくわからない。

tanamonの稀に良く書く日記

KEEP CALM AND DRINK BEER

MySQL5.6以降では時刻系の型に精度未満の値を入れると四捨五入される

トラブルが起きたコード

データの修正

CloudSQLでcreate tableが失敗する理由