自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

MySQL 時(shí)間戳類型真的了解嗎？早看早避坑！

作者： 五月君
數(shù)據(jù)庫 MySQL
日期類型是我們?cè)跀?shù)據(jù)庫操作中一個(gè)較為常見的數(shù)據(jù)類型，TIMESTAMP 類型相信使用的朋友也不少，但是你真的了解它嗎？

[[404586]]

本文轉(zhuǎn)載自微信公眾號(hào)「五月君」，作者五月君。轉(zhuǎn)載本文請(qǐng)聯(lián)系五月君公眾號(hào)。

日期類型是我們?cè)跀?shù)據(jù)庫操作中一個(gè)較為常見的數(shù)據(jù)類型，TIMESTAMP 類型相信使用的朋友也不少，但是你真的了解它嗎?

本文介紹在 MySQL 中使用 TIMESTAMP 類型遇到的一些潛在問題，最大時(shí)間限制相當(dāng)于埋在未來的坑、因?yàn)橄到y(tǒng)的一些默認(rèn)規(guī)則觸發(fā)日期自動(dòng)更新、默認(rèn)系統(tǒng)時(shí)區(qū)的性能問題，發(fā)現(xiàn)問題的同時(shí)，后面也推薦了一些在日期上個(gè)人認(rèn)為不錯(cuò)的方案，供參考。

安裝 MySQL

推薦 Docker 的方式本機(jī)安裝一個(gè) MySQL，步驟也很簡單，如下所示，對(duì)于學(xué)習(xí)還是很方便的，已安裝的可忽略。

  1. $ docker pull mysql 
  2. $ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql 
  3. $ docker exec -it mysql-test /bin/sh 
  4. $ mysql -h localhost -u root -p 

一個(gè)埋在未來的坑

假設(shè)，未來 2038 年某天的你，執(zhí)行了一條 SQL 更新了一個(gè)時(shí)間，第一次值為 '2038-01-19 03:14:07' 成功了，第二次值為 '2038-01-19 03:14:08' 報(bào)錯(cuò)了說傳的值是無效的，中間僅差了一秒，看著挺正常的一個(gè) SQL 啊!Why?

  1. # 第 1 次更新 
  2. $ UPDATE user SET birthday = '2038-01-19 03:14:07'  WHERE id = 1; 
  3. Query OK, 0 rows affected (0.01 sec) 
  4. Rows matched: 1  Changed: 0  Warnings: 0 
  5.  
  6. # 第 2 次更新 
  7. $ UPDATE user SET birthday = '2038-01-19 03:14:08'  WHERE id = 1; 
  8.  
  9. ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'birthday' at row 1 

在 MySQL 中，由于 TIMESTAMP 類型占用的空間為 4 個(gè)字節(jié)，理論上其能夠存儲(chǔ)最大的日期為 “2038-01-19 03:14:07”，而在 MySQL 5.6 之后占用的內(nèi)存空間為 7 個(gè)字節(jié)，可以精確到毫秒、微秒，但是這個(gè)最大日期并沒有被改變。

所以我們上面多設(shè)置了一秒，就報(bào)錯(cuò)了，對(duì)于系統(tǒng)而言，哪怕多一點(diǎn)也是不行的，超了就是超了。

這個(gè)限制在 MySQL 官方 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[1] 也有描述：

  1. The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. 

小心 TIMESTAMP 的自動(dòng)更新

假設(shè)一張表有 name、birthday 這些字段，這里的自動(dòng)更新是指當(dāng)你修改了表中 name 這個(gè)字段，但是最后發(fā)現(xiàn) birthday 這個(gè)字段被更新為了系統(tǒng)的當(dāng)前時(shí)間。

并且這種情況并不總是會(huì)出現(xiàn)，它和 MySQL 系統(tǒng)里的一個(gè)規(guī)則 **explicit_defaults_for_timestamp** 有關(guān),默認(rèn)情況下該參數(shù)的值為 OFF。

通過以下命令查看。

  1. $ show variables like '%explicit_defaults_for_timestamp%'; 
  2. +---------------------------------+-------+ 
  3. | Variable_name                   | Value | 
  4. +---------------------------------+-------+ 
  5. | explicit_defaults_for_timestamp | OFF   | 
  6. +---------------------------------+-------+ 

但是，這里容易潛在的埋一些坑，有些 MySQL 鏡像直接將這個(gè)值改為了 ON 就是禁用了功能。例如，通過上面 Docker 方式安裝的就已經(jīng)禁用了該功能。

問題復(fù)現(xiàn)

為了復(fù)現(xiàn)和講解這個(gè)問題，現(xiàn)在我需要將這個(gè)功能給放開，使用如下命令。

  1. $ SET @@SESSION.explicit_defaults_for_timestamp = 'ON'; 

首先，讓我們先創(chuàng)建一個(gè)數(shù)據(jù)庫，和一個(gè) user 表，注意下目前對(duì)生日字段的定義為 birthday TIMESTAMP NOT NULL。

  1. $ CREATE DATABASE test; 
  2. $ CREATE TABLE user( 
  3.   id BIGINT NOT NULL AUTO_INCREMENT, 
  4.   name VARCHAR(20) NOT NULL, 
  5.   birthday TIMESTAMP NOT NULL, 
  6.   PRIMARY KEY (id) 
  7. ); 

執(zhí)行 DESC user; 命令，查看當(dāng)前的表結(jié)構(gòu)，發(fā)現(xiàn) birthday 字段 Extra 這一列多了一些定義，Why?

  1. DESC user; 
  2. +----------+-------------+------+-----+-------------------+-----------------------------------------------+ 
  3. | Field    | Type        | Null | Key | Default           | Extra                                         | 
  4. +----------+-------------+------+-----+-------------------+-----------------------------------------------+ 
  5. | id       | bigint      | NO   | PRI | NULL              | auto_increment                                | 
  6. | name     | varchar(20) | NO   |     | NULL              |                                               | 
  7. | birthday | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | 
  8. +----------+-------------+------+-----+-------------------+-----------------------------------------------+ 

這一塊有個(gè)默認(rèn)的規(guī)則，當(dāng) explicit_defaults_for_timestamp 這個(gè)規(guī)則開啟時(shí)，創(chuàng)建表指定的 TIMESTAMP 類型的第一列，如果沒有顯示的使用 NULL 或 DEFAULT 或 ON UPDATE 聲明，表創(chuàng)建成功之后，會(huì)自動(dòng)為我們帶上 **DEFAULT_GENERATED on update CURRENT_TIMESTAMP** 屬性聲明。對(duì)應(yīng)我們的示例就是上面定義的 birthday TIMESTAMP NOT NULL。

如果設(shè)置為這樣子，意思是修改數(shù)據(jù)，會(huì)把該類型對(duì)應(yīng)的字段變?yōu)閿?shù)據(jù)庫當(dāng)前的系統(tǒng)日期。

改規(guī)則下，并且一張表中僅有一個(gè)字段可以擁有該特性，如果設(shè)置兩個(gè)會(huì)報(bào)錯(cuò)。

  1. $ CREATE TABLE user( 
  2.   birthday TIMESTAMP NOT NULL, 
  3.   utime TIMESTAMP NOT NULL, 
  4. ); 
  5.  
  6. // 運(yùn)行之后會(huì)得到一個(gè) show variables like '%explicit_defaults_for_timestamp%'; 錯(cuò)誤。 

往 user 表中插入一條數(shù)據(jù)。

  1. $ INSERT INTO user(name, birthday) VALUES('Tom', NOW(6)); 

假設(shè)，目前時(shí)間點(diǎn)為 T1(當(dāng)前 T1 的時(shí)間為 2021-01-01 06:10:27)，查看當(dāng)前 user 表中的數(shù)據(jù)。

  1. $ SELECT * FROM user; 
  2. +----+------+---------------------+ 
  3. | id | name | birthday            | 
  4. +----+------+---------------------+ 
  5. |  1 | Tom  | 2021-06-06 06:10:27 | 
  6. +----+------+---------------------+ 

假設(shè)，目前時(shí)間點(diǎn)為 T2(當(dāng)前 T2 的時(shí)間為 2021-01-01 06:13:06) 更新 user 表中的 name 為 Tom2，看返回結(jié)果 Changed: 1 更新是成功的。

  1. $ UPDATE user SET name = 'Tom2' WHERE id = 1; 
  2. Query OK, 1 row affected (0.02 sec) 
  3. Rows matched: 1  Changed: 1  Warnings: 0 

再次查詢，發(fā)現(xiàn) birthday 字段的值被改變?yōu)榱?T2 這個(gè)時(shí)間點(diǎn)，但是明明上面的 SQL 語句沒有寫更新 birthday 這個(gè)字段啊!Why?

  1. $ SELECT * FROM user; 
  2. +----+------+---------------------+ 
  3. | id | name | birthday            | 
  4. +----+------+---------------------+ 
  5. |  1 | Tom2 | 2021-06-06 06:13:06 | 
  6. +----+------+---------------------+ 

解決方案

當(dāng) explicit_defaults_for_timestamp 這個(gè)規(guī)則開啟時(shí)(其值為 OFF)，如果我們沒有對(duì) TIMESTAMP 類型的字段顯性賦值，更新時(shí)系統(tǒng)會(huì)為我們默認(rèn)設(shè)置為系統(tǒng)當(dāng)前時(shí)間。

如果不清楚這個(gè)問題，查找起來簡直讓人崩潰，明明 SQL 語句沒有，還是被更新了。

大多數(shù)情況下，這并非我們想要的情況，怎么禁用?

方法一：修改系統(tǒng)參數(shù)

將 explicit_defaults_for_timestamp 的值修改為 'ON' 禁用掉該屬性。

正在運(yùn)行的，可以使用 SET @@SESSION.explicit_defaults_for_timestamp = 'ON'; 修改。這里又一個(gè)坑，經(jīng)測(cè)試驗(yàn)證一旦表已創(chuàng)建，在設(shè)置是無效的。如果是在禁用該規(guī)則后創(chuàng)建的表，是可以的。

方法二：修改表結(jié)構(gòu)

對(duì)于那些線上正在運(yùn)行的無法修改的，總不能直接把表刪了再改吧。

當(dāng) explicit_defaults_for_timestamp 屬性為 OFF 的情況下也有兩種方法可以禁用，需要修改表結(jié)構(gòu)。

  1. // 指定該列為 NULL，例如 
  2. $ ALTER TABLE user MODIFY birthday TIMESTAMP NULL。 
  3.  
  4. // 使用 DEFAULT 為該列指定一個(gè)默認(rèn)值，例如 
  5. $ ALTER TABLE user MODIFY birthday TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 

最后，根據(jù) MYSQL 官網(wǎng)文檔 sysvar_explicit_defaults_for_timestamp[2] 的描述，這個(gè)非標(biāo)準(zhǔn)行為已被棄用，希望它們?cè)谖磥淼?MYSQL 版本中被刪除。確實(shí)挺坑的一個(gè)行為，如果不熟讀文檔，很容易踩坑。

TIMESTAMP 性能問題

TIMESTAMP 類型支持時(shí)區(qū)轉(zhuǎn)換，這個(gè)有利也有弊，當(dāng)默認(rèn)為操作系統(tǒng)時(shí)區(qū)時(shí)(time_zone=SYSTEM)，查詢系統(tǒng) TIMESTAMP 類型的字段會(huì)調(diào)用系統(tǒng)時(shí)區(qū)做時(shí)區(qū)轉(zhuǎn)換。而這個(gè)系統(tǒng)時(shí)區(qū)需要加鎖來保證此時(shí)的操作系統(tǒng)時(shí)區(qū)沒有被修改。

當(dāng)出現(xiàn)并發(fā)訪問時(shí)，勢(shì)必會(huì)出現(xiàn)資源競爭，多線程的上下文切換消耗，性能也會(huì)出現(xiàn)下降，下文我們做個(gè)性能測(cè)試。

查看當(dāng)前的時(shí)區(qū)信息，time_zone=SYSTEM 表示此時(shí)是操作系統(tǒng)的時(shí)區(qū)。

  1. $ show variables like "%time_zone%"; 
  2. +------------------+--------+ 
  3. | Variable_name    | Value  | 
  4. +------------------+--------+ 
  5. | system_time_zone | UTC    | 
  6. | time_zone        | SYSTEM | 
  7. +------------------+--------+ 

時(shí)區(qū)修改

MySQL 默認(rèn)使用系統(tǒng)時(shí)區(qū)，修改方法大致分為兩種：使用 SQL 命令臨時(shí)修改，修改配置文件這種是永久修改。

  1. # SQL 命令修改 
  2. $ SET time_zone = 'Asia/Shanghai'; 
  3.  
  4. # 配置文件 
  5. $ vim /etc/mysql/my.cnf 
  6. default-time_zone = 'Asia/Shanghai' 

如果使用 Docker 的可以在 docker run 時(shí)修改。通過 -e TZ='Asia/Shanghai' 指定時(shí)區(qū)，但是這樣發(fā)現(xiàn) 雖然 SELECT NOW() 沒問題，但是執(zhí)行 show variables like "%time_zone%" 命令 time_zone 還是顯示的 SYSTEM。

  1. $ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e TZ='Asia/Shanghai' mysql 

推薦修改文件，首先進(jìn)入容器內(nèi)執(zhí)行 cat /etc/mysql/conf.d/mysql.cnf 命令查看默認(rèn)配置，拷貝一份到自己的本機(jī)電腦，在執(zhí)行 docker run 時(shí)掛載到容器內(nèi)，這種方式好處是當(dāng)你有多個(gè)配置需要修改時(shí)，都可以在配置文件里改。

配置文件也許是這樣的：

  1. [mysqld] 
  2. pid-file        = /var/run/mysqld/mysqld.pid 
  3. socket          = /var/run/mysqld/mysqld.sock 
  4. datadir         = /var/lib/mysql 
  5. secure-file-priv= NULL 
  6. default-time_zone = 'Asia/Shanghai' 
  7.  
  8. # Custom config should go here 
  9. !includedir /etc/mysql/conf.d/ 

最終 docker run 命令如下：

  1. # 注意 /${root}/mysql.cnf 這個(gè)是你本機(jī)的配置地址 
  2. $ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /${root}/mysql.cnf:/etc/mysql/my.cnf mysql 

性能測(cè)試

MySQL 自帶了一個(gè)壓力測(cè)試工具 mysqlslap，可以模擬多個(gè)并發(fā)客戶端來對(duì) MySQL 做壓力測(cè)試，還是挺不錯(cuò)的，寫一些功能，想測(cè)試下基本的性能時(shí)還是可以用用的。

以下這個(gè)語句的意思是模擬 100 個(gè)客戶端并發(fā)，共執(zhí)行 100,0000 萬次查詢。

  1. # --number-of-queries 總的測(cè)試查詢次數(shù) 
  2. # -c 并發(fā)量，模擬多個(gè)客戶端執(zhí)行，下例模擬多個(gè)客戶端執(zhí)行 “SELECT NOW()” 
  3. # --create-schema 代表自定義的測(cè)試庫名稱，就是 MySQL 中的數(shù)據(jù)庫名稱 
  4. $ mysqlslap -u root -p --number-of-queries=1000000 --create-schema=test -c 100 --query='SELECT NOW()' 

下面是基于 mysqlslap 做的性能測(cè)試結(jié)果，在不同的時(shí)區(qū)下，分別所耗時(shí)間，單位(秒)，很明顯系統(tǒng)時(shí)區(qū)耗時(shí)更長些，兩者直接的相差為 25%。這只是耗時(shí)上的差異，CPU 信息我沒有去看。還有不同的電腦，測(cè)試出來的性能差距也會(huì)有差異。

------ System Asia/Shanghai difference
Average number of seconds to run all queries 35.55s 28.42s 25%

日期該怎么選擇?

MySQL 中日期類型存儲(chǔ)通常有 3 中方案，使用 INT、TIMESTAMP、DATETIME 下面分別簡單總結(jié)下。

INT 類型

INT 類型來存儲(chǔ)日期類型，存儲(chǔ)的就是時(shí)間戳類型，例如 2021-01-01 06:10:27 的時(shí)間戳為 1609452627000。

數(shù)據(jù)庫實(shí)際存儲(chǔ)的是一串?dāng)?shù)字，這種好處是沒有時(shí)間上下范圍限制，性能也比 TIMESTAMP 好，但是這種性能是收效甚微的，一個(gè)不友好的問題是，當(dāng)我們想查看數(shù)據(jù)做一些問題排查或數(shù)據(jù)分析時(shí)，通常不是很直觀的。

TIMESTAMP 類型

TIMESTAMP 類型在存儲(chǔ)時(shí)會(huì)先將本地時(shí)區(qū)時(shí)間轉(zhuǎn)換為 UTC 的時(shí)區(qū)時(shí)間，再講 UTC 時(shí)區(qū)時(shí)間轉(zhuǎn)為 4 字節(jié) INT 類型存儲(chǔ)，本質(zhì)是和 INT 一樣的，都是存儲(chǔ)為毫秒數(shù)。讀取時(shí)再次反向的轉(zhuǎn)換為時(shí)間戳 TIMESTAMP 類型，會(huì)做一些時(shí)間的格式化，看起來更直觀些。

TIMESTAMP 類型比較大的一個(gè)問題是有最大的時(shí)間限制，能夠有效存儲(chǔ)的時(shí)間范圍為 “'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'”，2038 年這個(gè)時(shí)間說遠(yuǎn)也是很快的，這個(gè)是需要考慮的，別為將來埋坑。

TIMESTAMP 類型盡管 5.6 版本之后支持精確到微笑，毫秒后面 6 為，但是 2038 最大時(shí)間限制這個(gè)問題并沒有解決。

它還有一個(gè)筆者個(gè)人認(rèn)為隱藏很的問題是，當(dāng)你把一個(gè)字段的定義為 birthday DATETIME NOT NULL 且觸發(fā)了它的自動(dòng)更新規(guī)則時(shí)，很容易掉坑里?？膳碌氖情_發(fā)和生產(chǎn)環(huán)境配置不一致，這種問題前期就發(fā)現(xiàn)不了，除非踩過這個(gè)坑。

DATETIME 類型

DATETIME 這個(gè)類型是筆者比較推薦的，它占用 8 個(gè)字節(jié)，能存儲(chǔ)的精確度為微妙，聲明類型時(shí)通過 DATETIME(6) 指定。

它的時(shí)間范圍為 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 這個(gè)時(shí)間目前是夠我們用的了。當(dāng)然，你要說我要存儲(chǔ) “三國時(shí)期張飛” 什么時(shí)候出生，那這 160 年生日也是存儲(chǔ)不了的。

DATETIME 類型它不會(huì)存儲(chǔ)時(shí)區(qū)信息，當(dāng)然這個(gè)問題，也不一定義非要在數(shù)據(jù)層解決不可，也不是什么大不了的問題，想做這種國際化的跨時(shí)區(qū)的，由中間層服務(wù)(Node.js 就很適合)統(tǒng)一解決也可。我認(rèn)為這個(gè)日期類型它能解決上面我們使用 TIMESTAMP 遇到的那些問題。

修改上面 user 表結(jié)構(gòu)，將日期類型統(tǒng)一聲明為 DATETIME 類型。

  • birthday 字段由用戶自定義傳入，指定為非空，DATETIME 這樣聲明精確為秒。
  • ctime 字段默認(rèn)當(dāng)前時(shí)間，僅在創(chuàng)建時(shí)指定，時(shí)間精確到微秒。
  • utime 字段記錄每一次的更新時(shí)間，這個(gè)不受 explicit_defaults_for_timestamp 參數(shù)影響并且也是在我們顯示的定義了 ON UPDATE... 之后才觸發(fā)自動(dòng)更新。
  1. CREATE TABLE user( 
  2.   id BIGINT NOT NULL AUTO_INCREMENT, 
  3.   name VARCHAR(20) NOT NULL, 
  4.   birthday DATETIME NOT NULL, 
  5.   ctime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), 
  6.   utime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), 
  7.   PRIMARY KEY (id) 
  8. ); 

假設(shè)，我們插入一條數(shù)據(jù)，birthday 傳入 2039 年使用 DATETIME 是沒問題的，同時(shí)也可以看下 ctime、utime 時(shí)間，這個(gè)精確度也是我們定義的。

  1. +----+------+---------------------+----------------------------+----------------------------+ 
  2. | id | name | birthday            | ctime                      | utime                      | 
  3. +----+------+---------------------+----------------------------+----------------------------+ 
  4. |  1 | Tom  | 2039-01-01 22:00:28 | 2021-01-01 22:00:28.112048 | 2021-01-01 22:00:28.112048 | 
  5. +----+------+---------------------+----------------------------+----------------------------+ 

參考資料

[1]11.2.2 The DATE, DATETIME, and TIMESTAMP Types: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

[2]sysvar_explicit_defaults_for_timestamp: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

 

責(zé)任編輯：武曉燕 來源： 五月君
MySQL時(shí)間戳類型
相關(guān)推薦

2023-06-12 07:00:40

Rust進(jìn)度任務(wù)

2022-01-28 07:58:41

WPS數(shù)據(jù)整理

2014-09-17 16:34:00

APP

2020-05-12 08:16:43

Elasticsear數(shù)據(jù)Lucene

2023-05-23 07:51:57

硬盤顆粒SSD

2010-10-26 13:29:28

2010-02-24 16:10:26

2021-11-26 08:07:16

MySQL SQL 語句數(shù)據(jù)庫

2022-07-26 00:00:22

HTAP系統(tǒng)數(shù)據(jù)庫

2020-01-15 10:17:41

Kubernetes容器負(fù)載均衡

2014-04-17 16:42:03

DevOps

2012-11-09 13:36:20

HPC氣象預(yù)測(cè)大雪

2018-12-21 11:24:55

Java時(shí)間處理編程語言

2024-01-29 10:09:59

數(shù)據(jù)庫INT(3)INT(11)

2023-02-15 11:58:35

Tomcat設(shè)計(jì)模式

2021-01-15 07:44:21

SQL注入攻擊黑客

2021-11-09 09:48:13

Logging python模塊

2019-09-16 08:40:42

2014-11-28 10:31:07

Hybrid APP
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)