主頁 > 知識庫 > MySQL時(shí)間設(shè)置注意事項(xiàng)的深入總結(jié)

MySQL時(shí)間設(shè)置注意事項(xiàng)的深入總結(jié)

熱門標(biāo)簽:武漢電銷機(jī)器人電話 呂梁外呼系統(tǒng) 南太平洋地圖標(biāo)注 北京金倫外呼系統(tǒng) 催天下外呼系統(tǒng) 400電話辦理服務(wù)價(jià)格最實(shí)惠 大豐地圖標(biāo)注app html地圖標(biāo)注并導(dǎo)航 400電話變更申請

時(shí)間真的存在嗎?有觀點(diǎn)認(rèn)為,時(shí)間只是人類構(gòu)想出來的一種概念,是用來衡量事物變化的標(biāo)準(zhǔn)。對于數(shù)據(jù)庫來說時(shí)間伴隨著數(shù)據(jù)并進(jìn)。進(jìn)入MySQL時(shí)間漩渦中看看。

1.時(shí)間類型的字段

MySQL時(shí)間類型字段:

下面的容易忽略的內(nèi)容:

TIMESTAMP保存數(shù)據(jù)方式:

MySQL將TIMESTAMP值從當(dāng)前時(shí)區(qū)轉(zhuǎn)換為UTC進(jìn)行存儲(chǔ),并從UTC返回到當(dāng)前時(shí)區(qū)進(jìn)行檢索。
(這不適用于其他類型,比如DATETIME。)默認(rèn)情況下,每個(gè)連接的當(dāng)前時(shí)區(qū)是服務(wù)器的時(shí)間。時(shí)區(qū)可以在每個(gè)連接的基礎(chǔ)上設(shè)置。只要時(shí)區(qū)設(shè)置保持不變,就會(huì)返回所存儲(chǔ)的相同值。如果存儲(chǔ)一個(gè)時(shí)間戳值,然后更改時(shí)區(qū)并檢索該值,則檢索到的值與存儲(chǔ)的值不同。出現(xiàn)這種情況是因?yàn)闆]有在兩個(gè)方向上使用相同的時(shí)區(qū)進(jìn)行轉(zhuǎn)換。當(dāng)前時(shí)區(qū)可以作為time_zone系統(tǒng)變量的值。

TIMESTAMP和SQL_MODE組合

sql_mode也會(huì)影響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限制值還是插入進(jìn)去了,但采用的是0填空方式。
對于STRICT_TRANS_TABLES, MySQL將一個(gè)無效的值轉(zhuǎn)換為最接近的有效值,然后插入調(diào)整后的值。如果缺少一個(gè)值,MySQL將為列數(shù)據(jù)類型插入隱式的默認(rèn)值。

2.explicit_defaults_for_timestamp時(shí)間處理機(jī)制

默認(rèn)情況是啟用。

在MySQL 8.0.22中,如果試圖在聲明為TIMESTAMP NOT NULL的列中插入NULL,將會(huì)被拒絕,并產(chǎn)生錯(cuò)誤。

1.explicit_defaults_for_timestamp被禁用時(shí):

  • 沒有使用NULL屬性顯式聲明的時(shí)間戳列將自動(dòng)使用NOT NULL屬性聲明。允許為這樣的列賦值為NULL,并將該列設(shè)置為當(dāng)前時(shí)間戳。在MySQL 8.0.22中,如果試圖在聲明為TIMESTAMP NOT NULL的列中插入NULL,將會(huì)被拒絕,并產(chǎn)生錯(cuò)誤。
  • 如果表中的第一列沒有使用NULL屬性或顯式的DEFAULT或ON UPDATE屬性進(jìn)行聲明,則會(huì)自動(dòng)使用默認(rèn)的CURRENT_TIMESTAMP屬性和ON UPDATE CURRENT_TIMESTAMP屬性進(jìn)行聲明。
  • TIMESTAMP 如果沒有顯式地使用NULL屬性或顯式默認(rèn)屬性聲明,則自動(dòng)聲明為默認(rèn)的'0000-00-00 00:00:00'(“零”時(shí)間戳)
  • 根據(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)前時(shí)間戳。要指定當(dāng)前時(shí)間戳,設(shè)置為CURRENT_TIMESTAMP或一個(gè)同義詞,比如NOW()。
  • 沒有使用not NULL屬性顯式聲明的TIMESTAMP列將自動(dòng)使用NULL屬性聲明并允許空值。
  • 使用NOT NULL屬性聲明的時(shí)間戳列不允許空值。對于為這樣的列指定NULL的插入,如果啟用了strict SQL模式,那么單行插入會(huì)出現(xiàn)錯(cuò)誤,或者禁用了strict SQL模式的多行插入會(huì)插入'0000-00-00 00:00:00'。在任何情況下,為列賦值為NULL都不會(huì)將其設(shè)置為當(dāng)前時(shí)間戳。
  • 使用NOT NULL屬性顯式聲明且沒有顯式默認(rèn)屬性的時(shí)間戳列被視為沒有默認(rèn)值。對于未為此類列指定顯式值的插入行,結(jié)果取決于SQL模式。如果啟用了嚴(yán)格SQL模式,則會(huì)出現(xiàn)錯(cuò)誤。如果沒有啟用嚴(yán)格的SQL模式,則使用默認(rèn)隱式值'0000-00-00 00:00:00'聲明該列,并出現(xiàn)警告。
  • timestamp類型字段 不會(huì)自動(dòng)使用默認(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個(gè):

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ù)器啟動(dòng)時(shí),它嘗試自動(dòng)確定主機(jī)的時(shí)區(qū),并使用它來設(shè)置system_time_zone系統(tǒng)變量。此后該值不會(huì)改變。

2.time_zone:全time_zone表示服務(wù)器當(dāng)前運(yùn)行的時(shí)區(qū)。初始的time_zone值為“SYSTEM”,表示服務(wù)器時(shí)區(qū)與系統(tǒng)時(shí)區(qū)一致。

  • 如果設(shè)置為SYSTEM, 如MySQL函數(shù)調(diào)用都會(huì)調(diào)用一個(gè)系統(tǒng)庫來確定當(dāng)前的系統(tǒng)時(shí)區(qū)。這個(gè)調(diào)用可能被一個(gè)全局互斥鎖保護(hù),從而導(dǎo)致爭用。CPU使用率高問題。
  • 設(shè)置會(huì)話時(shí)區(qū)會(huì)影響時(shí)區(qū)敏感的時(shí)間值的顯示和存儲(chǔ)。這包括NOW()或CURTIME()等函數(shù)顯示的值,以及存儲(chǔ)在時(shí)間戳列中的值和從時(shí)間戳列檢索到的值。時(shí)間戳列的值將從會(huì)話時(shí)區(qū)轉(zhuǎn)換為UTC用于存儲(chǔ),從UTC轉(zhuǎn)換為會(huì)話時(shí)區(qū)用于檢索。
  • 會(huì)話時(shí)區(qū)設(shè)置不影響UTC_TIMESTAMP()等函數(shù)顯示的值,也不影響DATE、time或DATETIME列中的值。這些數(shù)據(jù)類型的值也不存儲(chǔ)在UTC;時(shí)區(qū)僅在從時(shí)間戳值轉(zhuǎn)換時(shí)適用它們。

備注:mysql還提供時(shí)區(qū)導(dǎo)入到mysql系統(tǒng)庫的方法。通過mysql_tzinfo_to_sql程序加載/usr/share/zoneinfom下的時(shí)區(qū)信息。

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

##mysql_tzinfo_to_sql工具導(dǎo)入時(shí)區(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

這個(gè)變量控制寫入錯(cuò)誤日志的消息以及寫入文件的一般查詢?nèi)罩竞吐俨樵內(nèi)罩鞠⒅械臅r(shí)間戳的時(shí)區(qū)。

它不會(huì)影響一般查詢?nèi)罩镜臅r(shí)區(qū)和慢速查詢?nèi)罩鞠懭氡?mysql。general_log mysql.slow_log)。

允許的log_timestamps值是UTC(默認(rèn)值)和SYSTEM(本地系統(tǒng)時(shí)區(qū))

備注:UTC一般指協(xié)調(diào)世界時(shí)。協(xié)調(diào)世界時(shí),又稱世界統(tǒng)一時(shí)間、世界標(biāo)準(zhǔn)時(shí)間、國際協(xié)調(diào)時(shí)間,就是UTC+8小時(shí)=中國時(shí)間

當(dāng)然值 需要跟系統(tǒng)記錄時(shí)間一致,才能更好的管理。

#設(shè)置時(shí)區(qū),更改為東八區(qū)
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 |
+------------------+--------+

總結(jié)

從時(shí)間類型,參數(shù),系統(tǒng)時(shí)區(qū)了解到,MySQL里時(shí)間應(yīng)該怎樣設(shè)置和使用。

特別是無特殊要求sql_mode不要輕易改動(dòng)。

到此這篇關(guān)于MySQL時(shí)間設(shè)置注意事項(xiàng)的文章就介紹到這了,更多相關(guān)MySQL時(shí)間設(shè)置注意內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

您可能感興趣的文章:
  • 解析MySQL設(shè)置當(dāng)前時(shí)間為默認(rèn)值的方法
  • mysql 設(shè)置默認(rèn)的時(shí)間值
  • mysql 設(shè)置自動(dòng)創(chuàng)建時(shí)間及修改時(shí)間的方法示例

標(biāo)簽:迪慶 南充 西寧 無錫 龍巖 徐州 自貢 麗水

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL時(shí)間設(shè)置注意事項(xiàng)的深入總結(jié)》,本文關(guān)鍵詞  MySQL,時(shí)間,設(shè)置,注意事項(xiàng),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL時(shí)間設(shè)置注意事項(xiàng)的深入總結(jié)》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL時(shí)間設(shè)置注意事項(xiàng)的深入總結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章