解決MySQL時間轉換異常

解決MySQL時間轉換異常

串連MySQL資料庫，在操作值為0的timestamp類型時不能正確的處理，而是預設拋出一個異常：
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 7 to TIMESTAMP.

這個問題在官方文檔中有詳細說明，詳見如下連結：http://bugs.mysql.com/bug.php?id=19274
官方推薦在JDBC串連串中通過配置zeroDateTimeBehavior=convertToNull來處理，如下：

jdbc:mysql://localhost:3306/mydbname?zeroDateTimeBehavior=convertToNull

JDBC串連串zeroDateTimeBehavior屬性有下列三個屬性值：
1> exception：預設值，即拋出SQL state [S1009]. Cannot convert value....的異常；
2> convertToNull：將日期轉換成NULL值；
3> round：替換成最近的日期即0001-01-01；

程式角度避免

這類異常的觸發也與timestamp賦值的操作有關，如果能夠在設計階段和記錄寫入階段做好邏輯判斷，
避免寫入 '0000-00-00 00:00:00'這類值，那麼也可以避免出現 Cannot convert value '0000-00-00 00:00:00' from column N to TIMESTAMP的錯誤。
1> 插入的時候判斷：插入 0 時 變為 null,或者插入 0 的時候 改為 now();

2> 在查詢資料判斷：SELECT id, name,if(date = '0000-00-00 00:00:00', NULL , date ) AS date FROM `date`，這樣就會將值為 0000-00-00 00:00:00 轉換為 null
3> 設定sql_model：
mysql_query("set sql_mode='no_zero_date'");        # 如果在temestamp 列插入 0 會產生警告，但是資料還是會插入成功
mysql_query("set sql_mode='strict_all_tables,no_zero_date'");  # 如果插入 0會報如下錯誤：ERROR 1292 (22007): Incorrect datetime value: '0' for column 'date' at row 1

參考資料：
http://bugs.mysql.com/bug.php?id=19274

本文永久更新連結地址：