散亂格部落

最近在處理一個使用Hibernate + MySQL的Project時發現了一會在取得Database Result Set時返回Exception - Caused by: java.sql. ... SQLException: DAY_OF_MONTH
以下是在排查時的一些發現，現作一些筆記以後能作參考
Exception成因：經過排查及在網上查找相關訊息，最後得出兩個可能的原因。
1) DB中的Column length與Hibernate相關的PO定義不(也可能是Data Type不同)
2) DB中相關的Column存不一些Invalid Data，而這次的Exception明顯是與Date/Datetime的Column相關
最後經過排查發現是Data的問題，但這裡就有一個比較有趣的發現。
因為在以往的Project比較少使用MySQL，也比較少留意有關Datetime的Setting。
這次發現了雖然相關的Column Value都是一個正確的Datetime Format，但其中有一條紀錄的Date是"1990-01-00 00:00:00"，而這明顯是一個Invalid Date，所以引申了一個疑問 — 為什麼MySQL接受了這樣的資料?
經過查找後，終於在MySQL的官方文件中找到了答案。
 

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
在這份官方文件中寫到

• MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as '2009-00-00' or '2009-01-00'. However, with dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates. To disallow zero month or day parts in dates, enable the NO_ZERO_IN_DATE mode.

• MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” In some cases, this is more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

原來MySQL是準許Datetime輸入00為日子，即是可以輸入"0000-00-00 00:00:00"作為Dummy Data。然而這樣的Data到了Hibernate和Java時便會變成Invalid Date而Throw出Exception。
 

對此MySQL也有相關的設定對應(當然也可修改數據避免再Throw Exception)
只需在SQL_MODE中加入NO_ZERO_IN_DATE便可，下例
先找出現時的SQL_MODE設定
SELECT @@sql_mode;
或
SELECT @@GLOBAL.sql_mode;
MySQL的預設應是"STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
現時可加上
SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE';
便可設定DB不接受以0作Value來避免輸入Invalid Datetime