Mysql 資料庫日期 存儲精度問題

一、mysql 資料庫日期類型

名稱	描述	位元組數	格式	取值範圍	零值
year	表示時間	1	yyyy	1901-2155	0000
date	表示日期	4	yyyy-mm-dd	1000-01-01~9999-12-31	0000-00-00
time	表示一天中的時間	3	HH:mm:ss	-838:59:59~838:59:59	00:00:00
dateTime		8		1000-01-01 00:00:00~9999-12-31 23:59:59	0000-00-00 00:00:00
timestamp				19700101080001~20380119111407

二、常用的兩種

1、 timestamp

1. 5.7版本之前,不支援一張表中存在兩個預設值為CURRENT_TIMESTAMP 的timestamp 字段，

   也就是說如果有一個create_time 和 update_time ，兩個字段隻能有一個預設值為 CURRENT_TIMESTAMP，

   而且需要在第一個timestamp類型字段設定，值得注意的是如果目前的timestamp字段為非空的話預設是根據當時時間戳更新，

   如果目前字段是建立時間的話，每次修改資料可能導緻建立時間随之更新，需注意。

2. 5.7版本到之後的版本，一張表中timestamp字段都可以使用預設值CURRENT_TIMESTAMP，都可以根據目前時間戳更新。

   但是，在5.7版本中，timestamp 類型的字段預設是不允許設定為零日期的。（oracle 資料庫遷移到mysql5.7時遇到的問題）

修改步驟：

1. 執行指令:select @@sql_mode;
2. 可以看出有兩個字段 ：NO_ZERO_IN_DATE 和 NO_ZERO_DATE 兩個值，限制字段不能為零日期;

3. 修改配置檔案my.cnf,去掉這兩個值 ，并且添加一行:

   sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES。

1. mysql 的timestamp 值自動從目前時區轉換到utc時區存儲，并且自動從utc時區轉換為目前系統時區檢索傳回。

2、datetime

1. 在mysql5.7之後，datetime字段也可以指定預設值，并且格式和timestamp一樣。

三、資料庫時區

1、 檢視資料庫時區

• show variables like '%time_zone';

2、 system_time_zone

• 系統預設時區

3、 time_zone

• 目前使用的時區，預設使用的是系統時區

4、 修改時區

1. 通過my.conf 檔案修改

在 [mysqld] 之下加

1. 通過指令行修改

set time_zone='+8:00';

5、驗證時區修改是否正确

• select now();通過結果對比。

四、存儲精度問題

1. 在mysql5.6.4 之前，時間隻能精确儲存到秒;5.6.4~5.7 之間，精确到分數秒;5.7到之後，精确到微妙。

2. 在資料庫存儲中，如果字段類型是datetime或timestamp，長度可以設為3，增加精确度,最後可以設為6，不過一般三位就夠用了。

   值得注意的是，如果要加長度，如果資料庫版本過低，需要去掉 （根據目前時間戳更新），不然會報錯。