MySQL關(guān)于時間設(shè)置的注意事項
本文轉(zhuǎn)載自微信公眾號「數(shù)據(jù)和云」，作者數(shù)據(jù)和云。轉(zhuǎn)載本文請聯(lián)系數(shù)據(jù)和云公眾號。
時間真的存在嗎?有觀點認(rèn)為，時間只是人類構(gòu)想出來的一種概念，是用來衡量事物變化的標(biāo)準(zhǔn)。對于數(shù)據(jù)庫來說，時間伴隨著數(shù)據(jù)并進。讓我們進入MySQL時間漩渦中看一看。
1. 時間類型的字段
MySQL時間類型字段：
下面是容易忽略的內(nèi)容：
- TIMESTAMP保存數(shù)據(jù)方式：
MySQL將TIMESTAMP值從當(dāng)前時區(qū)轉(zhuǎn)換為UTC進行存儲，并從UTC返回到當(dāng)前時區(qū)進行檢索。(這不適用于其他類型，比如DATETIME。)默認(rèn)情況下，每個連接的當(dāng)前時區(qū)是服務(wù)器的時間。時區(qū)可以在每個連接的基礎(chǔ)上設(shè)置。只要時區(qū)設(shè)置保持不變，就會返回所存儲的相同值。如果存儲一個時間戳值，然后更改時區(qū)并檢索該值，則檢索到的值與存儲的值不同。出現(xiàn)這種情況是因為沒有在兩個方向上使用相同的時區(qū)進行轉(zhuǎn)換。當(dāng)前時區(qū)可以作為time_zone系統(tǒng)變量的值。
- 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將一個無效的值轉(zhuǎn)換為最接近的有效值，然后插入調(diào)整后的值。如果缺少一個值，MySQL將為列數(shù)據(jù)類型插入隱式的默認(rèn)值。
2. explicit_defaults_for_timestamp時間處理機制
默認(rèn)情況是啟用。
在MySQL 8.0.22中，如果試圖在聲明為TIMESTAMP NOT NULL的列中插入NULL，將會被拒絕，并產(chǎn)生錯誤。
1)explicit_defaults_for_timestamp被禁用時:
- 沒有使用NULL屬性顯式聲明的時間戳列將自動使用NOT NULL屬性聲明。允許為這樣的列賦值為NULL，并將該列設(shè)置為當(dāng)前時間戳。在MySQL 8.0.22中，如果試圖在聲明為TIMESTAMP NOT NULL的列中插入NULL，將會被拒絕，并產(chǎn)生錯誤。
- 如果表中的第一列沒有使用NULL屬性或顯式的DEFAULT或ON UPDATE屬性進行聲明，則會自動使用默認(rèn)的CURRENT_TIMESTAMP屬性和ON UPDATE CURRENT_TIMESTAMP屬性進行聲明。
- TIMESTAMP 如果沒有顯式地使用NULL屬性或顯式默認(rèn)屬性聲明，則自動聲明為默認(rèn)的’0000-00-00 00:00:00’(“零”時間戳)。
- 根據(jù)啟用的是strict SQL模式還是NO_ZERO_DATE SQL模式，默認(rèn)值“0000-00-00 00:00:00”可能無效。
2)explicit_defaults_for_timestamp被啟用:
- 不可能為TIMESTAMP指定NULL值來將其設(shè)置為當(dāng)前時間戳。要指定當(dāng)前時間戳，設(shè)置為CURRENT_TIMESTAMP或一個同義詞，比如NOW()。
- 沒有使用not NULL屬性顯式聲明的TIMESTAMP列將自動使用NULL屬性聲明并允許空值。
- 使用NOT NULL屬性聲明的時間戳列不允許空值。對于為這樣的列指定NULL的插入，如果啟用了strict SQL模式，那么單行插入會出現(xiàn)錯誤，或者禁用了strict SQL模式的多行插入會插入’0000-00-00 00:00:00’。在任何情況下，為列賦值為NULL都不會將其設(shè)置為當(dāng)前時間戳。
- 使用NOT NULL屬性顯式聲明且沒有顯式默認(rèn)屬性的時間戳列被視為沒有默認(rèn)值。對于未為此類列指定顯式值的插入行，結(jié)果取決于SQL模式。如果啟用了嚴(yán)格SQL模式，則會出現(xiàn)錯誤。如果沒有啟用嚴(yán)格的SQL模式，則使用默認(rèn)隱式值’0000-00-00 00:00:00’聲明該列，并出現(xiàn)警告。
- timestamp類型字段 不會自動使用默認(rèn)的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系統(tǒng)配置
系統(tǒng)相關(guān)事件參數(shù)包含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：
當(dāng)服務(wù)器啟動時，它嘗試自動確定主機的時區(qū)，并使用它來設(shè)置system_time_zone系統(tǒng)變量。此后該值不會改變。
2)time_zone：
全time_zone表示服務(wù)器當(dāng)前運行的時區(qū)。初始的time_zone值為“SYSTEM”，表示服務(wù)器時區(qū)與系統(tǒng)時區(qū)一致。
- 如果設(shè)置為SYSTEM，如MySQL函數(shù)調(diào)用都會調(diào)用一個系統(tǒng)庫來確定當(dāng)前的系統(tǒng)時區(qū)。這個調(diào)用可能被一個全局互斥鎖保護，從而導(dǎo)致爭用。CPU使用率高問題。
- 設(shè)置會話時區(qū)會影響時區(qū)敏感的時間值的顯示和存儲。這包括NOW()或CURTIME()等函數(shù)顯示的值，以及存儲在時間戳列中的值和從時間戳列檢索到的值。時間戳列的值將從會話時區(qū)轉(zhuǎn)換為UTC用于存儲，從UTC轉(zhuǎn)換為會話時區(qū)用于檢索。
- 會話時區(qū)設(shè)置不影響UTC_TIMESTAMP()等函數(shù)顯示的值，也不影響DATE、time或DATETIME列中的值。這些數(shù)據(jù)類型的值也不存儲在UTC;時區(qū)僅在從時間戳值轉(zhuǎn)換時適用它們。
備注：MySQL還提供時區(qū)導(dǎo)入到MySQL系統(tǒng)庫的方法。通過mysql_tzinfo_to_sql程序加載/usr/share/zoneinfom下的時區(qū)信息。
- mysql> SELECT COUNT(*) FROM mysql.time_zone_name;
- +----------+
- | COUNT(*) |
- +----------+
- | 0 |
- +----------+
##mysql_tzinfo_to_sql工具導(dǎo)入時區(qū)值。
- 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
- 這個變量控制寫入錯誤日志的消息以及寫入文件的一般查詢?nèi)罩竞吐俨樵內(nèi)罩鞠⒅械臅r間戳的時區(qū)。
- 它不會影響一般查詢?nèi)罩镜臅r區(qū)和慢速查詢?nèi)罩鞠懭氡?mysql。general_log mysql.slow_log)。
- 允許的log_timestamps值是UTC(默認(rèn)值)和SYSTEM(本地系統(tǒng)時區(qū))。
備注：UTC一般指協(xié)調(diào)世界時。協(xié)調(diào)世界時，又稱世界統(tǒng)一時間、世界標(biāo)準(zhǔn)時間、國際協(xié)調(diào)時間，就是UTC+8小時=中國時間。當(dāng)然值需要跟系統(tǒng)記錄時間一致，才能更好地管理。
- #設(shè)置時區(qū)，更改為東八區(qū)
- SET GLOBAL time_zone = '+8:00';
建議：
- [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 |
- +------------------+--------+
總結(jié)
從時間類型、參數(shù)、系統(tǒng)時區(qū)了解到，MySQL里時間應(yīng)該怎樣設(shè)置和使用，特別是無特殊要求，sql_mode不要輕易改動。
關(guān)于作者
崔虎龍，云和恩墨MySQL技術(shù)顧問，長期服務(wù)于金融、游戲、物流等行業(yè)的數(shù)據(jù)中心，設(shè)計數(shù)據(jù)存儲架構(gòu)，并熟悉數(shù)據(jù)中心運營管理的流程及規(guī)范，自動化運維等。擅長MySQL、Redis、MongoDB數(shù)據(jù)庫高可用設(shè)計和運維故障處理、備份恢復(fù)、升級遷移、性能優(yōu)化。自學(xué)通過了MySQL OCP 5.6和MySQL OCP 5.7認(rèn)證。2年多開發(fā)經(jīng)驗，10年數(shù)據(jù)庫運維工作經(jīng)驗，其中專職做MySQL工作8年;曾經(jīng)擔(dān)任過項目經(jīng)理、數(shù)據(jù)庫經(jīng)理、數(shù)據(jù)倉庫架構(gòu)師、MySQL技術(shù)專家、DBA等職務(wù);涉及行業(yè)：金融(銀行、理財)、物流、游戲、醫(yī)療、重工業(yè)等。
墨天輪原文鏈接：https://www.modb.pro/db/53474
