MySQL時間設置註意事項的深入總結

Posted on by

時間真的存在嗎？有觀點認為，時間隻是人類構想出來的一種概念，是用來衡量事物變化的標準。對於數據庫來說時間伴隨著數據並進。進入MySQL時間漩渦中看看。

1.時間類型的字段

MySQL時間類型字段：

下面的容易忽略的內容：

TIMESTAMP保存數據方式：

MySQL將TIMESTAMP值從當前時區轉換為UTC進行存儲，並從UTC返回到當前時區進行檢索。
(這不適用於其他類型，比如DATETIME。)默認情況下，每個連接的當前時區是服務器的時間。時區可以在每個連接的基礎上設置。隻要時區設置保持不變，就會返回所存儲的相同值。如果存儲一個時間戳值，然後更改時區並檢索該值，則檢索到的值與存儲的值不同。出現這種情況是因為沒有在兩個方向上使用相同的時區進行轉換。當前時區可以作為time_zone系統變量的值。

TIMESTAMP和SQL_MODE組合

sql_mode也會影響timestamp值：

mysql> CREATE TABLE ts (
         id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
         col TIMESTAMP NOT NULL
     ) AUTO_INCREMENT = 1;

mysql> SHOW VARIABLES LIKE  '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
mysql>  INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10');
ERROR 1292 (22007): Incorrect datetime value: '1969-01-01 01:01:10' for column 'col' at row 1

mysql> SET sql_mode="";
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE  '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
mysql>  INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10'),('2999-01-01 01:01:10');
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 2
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col' at row 1 |
| Warning | 1264 | Out of range value for column 'col' at row 2 |
+---------+------+----------------------------------------------+

mysql> SELECT * FROM TS;
+----+---------------------+
| id | col                 |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
|  2 | 0000-00-00 00:00:00 |
+----+---------------------+
2 rows in set (0.00 sec)

通過控制sql_mode，超出timestamp限制值還是插入進去瞭，但采用的是0填空方式。
對於STRICT_TRANS_TABLES, MySQL將一個無效的值轉換為最接近的有效值，然後插入調整後的值。如果缺少一個值，MySQL將為列數據類型插入隱式的默認值。

2.explicit_defaults_for_timestamp時間處理機制

默認情況是啟用。

在MySQL 8.0.22中，如果試圖在聲明為TIMESTAMP NOT NULL的列中插入NULL，將會被拒絕，並產生錯誤。

1.explicit_defaults_for_timestamp被禁用時:

  • 沒有使用NULL屬性顯式聲明的時間戳列將自動使用NOT NULL屬性聲明。允許為這樣的列賦值為NULL，並將該列設置為當前時間戳。在MySQL 8.0.22中，如果試圖在聲明為TIMESTAMP NOT NULL的列中插入NULL，將會被拒絕，並產生錯誤。
  • 如果表中的第一列沒有使用NULL屬性或顯式的DEFAULT或ON UPDATE屬性進行聲明，則會自動使用默認的CURRENT_TIMESTAMP屬性和ON UPDATE CURRENT_TIMESTAMP屬性進行聲明。
  • TIMESTAMP 如果沒有顯式地使用NULL屬性或顯式默認屬性聲明，則自動聲明為默認的’0000-00-00 00:00:00′(“零”時間戳)
  • 根據啟用的是strict SQL模式還是NO_ZERO_DATE SQL模式，默認值“0000-00-00 00:00:00”可能無效。

2.explicit_defaults_for_timestamp被啟用:

  • 不可能為TIMESTAMP指定NULL值來將其設置為當前時間戳。要指定當前時間戳，設置為CURRENT_TIMESTAMP或一個同義詞，比如NOW()。
  • 沒有使用not NULL屬性顯式聲明的TIMESTAMP列將自動使用NULL屬性聲明並允許空值。
  • 使用NOT NULL屬性聲明的時間戳列不允許空值。對於為這樣的列指定NULL的插入，如果啟用瞭strict SQL模式，那麼單行插入會出現錯誤，或者禁用瞭strict SQL模式的多行插入會插入’0000-00-00 00:00:00’。在任何情況下，為列賦值為NULL都不會將其設置為當前時間戳。
  • 使用NOT NULL屬性顯式聲明且沒有顯式默認屬性的時間戳列被視為沒有默認值。對於未為此類列指定顯式值的插入行，結果取決於SQL模式。如果啟用瞭嚴格SQL模式，則會出現錯誤。如果沒有啟用嚴格的SQL模式，則使用默認隱式值’0000-00-00 00:00:00’聲明該列，並出現警告。
  • timestamp類型字段 不會自動使用默認的CURRENT_TIMESTAMP屬性或更新CURRENT_TIMESTAMP屬性聲明。這些屬性必須顯式指定。

測試：

CREATE TABLE `test1`(
id bigint not null AUTO_INCREMENT COMMENT '主鍵ID', 
name varchar(20) COMMENT '主鍵ID',
create_time TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'cr time',
PRIMARY KEY(id)
)ENGINE=InnoDB  AUTO_INCREMENT=1 ;
SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';
SET  GLOBAL  explicit_defaults_for_timestamp=ON;
SET  GLOBAL  explicit_defaults_for_timestamp=OFF;
INSERT INTO test1(id,name,create_time) VALUES(1,'Kit',NULL);

3.mysql系統配置

系統相關事件參數包含3個：

mysql>show global variables where Variable_name like '%time_zone%' or Variable_name like 'log_timestamp%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
| log_timestamps   | UTC    |
+------------------+--------+
3 rows in set (0.00 sec)

1.system time zone：當服務器啟動時，它嘗試自動確定主機的時區，並使用它來設置system_time_zone系統變量。此後該值不會改變。

2.time_zone：全time_zone表示服務器當前運行的時區。初始的time_zone值為“SYSTEM”，表示服務器時區與系統時區一致。

  • 如果設置為SYSTEM， 如MySQL函數調用都會調用一個系統庫來確定當前的系統時區。這個調用可能被一個全局互斥鎖保護，從而導致爭用。CPU使用率高問題。
  • 設置會話時區會影響時區敏感的時間值的顯示和存儲。這包括NOW()或CURTIME()等函數顯示的值，以及存儲在時間戳列中的值和從時間戳列檢索到的值。時間戳列的值將從會話時區轉換為UTC用於存儲，從UTC轉換為會話時區用於檢索。
  • 會話時區設置不影響UTC_TIMESTAMP()等函數顯示的值，也不影響DATE、time或DATETIME列中的值。這些數據類型的值也不存儲在UTC;時區僅在從時間戳值轉換時適用它們。

備註：mysql還提供時區導入到mysql系統庫的方法。通過mysql_tzinfo_to_sql程序加載/usr/share/zoneinfom下的時區信息。

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

##mysql_tzinfo_to_sql工具導入時區值

shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;
+----------+
| COUNT(*) |
+----------+
|     1780 |
+----------+

3.log_timestamps

這個變量控制寫入錯誤日志的消息以及寫入文件的一般查詢日志和慢速查詢日志消息中的時間戳的時區。

它不會影響一般查詢日志的時區和慢速查詢日志消息寫入表(mysql。general_log mysql.slow_log)。

允許的log_timestamps值是UTC(默認值)和SYSTEM(本地系統時區)

備註：UTC一般指協調世界時。協調世界時，又稱世界統一時間、世界標準時間、國際協調時間，就是UTC+8小時=中國時間

當然值 需要跟系統記錄時間一致，才能更好的管理。

#設置時區，更改為東八區
SET GLOBAL time_zone = '+8:00';

建議：

mysql配置文件my.cnf

[mysqld]
log_timestamps=SYSTEM
default-time_zone                  = '+8:00'

mysql>show global variables where Variable_name like '%time_zone%' or Variable_name like 'log_timestamp%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| log_timestamps   | SYSTEM |
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+

總結

從時間類型，參數，系統時區瞭解到，MySQL裡時間應該怎樣設置和使用。

特別是無特殊要求sql_mode不要輕易改動。

到此這篇關於MySQL時間設置註意事項的文章就介紹到這瞭,更多相關MySQL時間設置註意內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：