前沿

最近工作中遇到兩例mysql時(shí)間戳相關(guān)的問(wèn)題，一個(gè)是mysql-connector-java和msyql的精度不一致導(dǎo)致數(shù)據(jù)查不到；另一例是應(yīng)用服務(wù)器時(shí)區(qū)錯(cuò)誤導(dǎo)致數(shù)據(jù)查詢不到。通過(guò)這篇文章，希望能夠解答關(guān)于mysql中時(shí)間戳的幾個(gè)問(wèn)題：

1. mysql中的DATETIME精度為什么只支持到秒？
2. mysql中的DATETIME類型跟時(shí)區(qū)有關(guān)嗎？
3. mysql設(shè)計(jì)表的時(shí)候，表示時(shí)間的字段該如何選擇？

案例分析 DATETIME的精度問(wèn)題

在我負(fù)責(zé)的應(yīng)用中，有個(gè)功能需要用到類似下面這種SQL，即使用時(shí)間戳作為查詢的條件，查詢?cè)谀硞€(gè)時(shí)間戳之后的所有數(shù)據(jù)。

select * from adu_user where gmt_modified >= #{date} 

前段時(shí)間，將負(fù)責(zé)的應(yīng)用的mysql-connector-java的版本從5.1.16升級(jí)到5.1.30，在做功能回歸的時(shí)候發(fā)現(xiàn)，使用了類似上面的SQL的用例的運(yùn)行時(shí)數(shù)據(jù)會(huì)有遺漏，導(dǎo)致功能有問(wèn)題。

經(jīng)過(guò)排查發(fā)現(xiàn)：mysql-connector-java在5.1.23之前會(huì)將秒后面的精度丟棄再傳給MySQL服務(wù)端，正好我們使用的mysql版本中DATETIME的精度是秒；在我將mysql-connector-java升級(jí)到5.1.30后，從java應(yīng)用通過(guò)mysql-connector-java將時(shí)間戳傳到MySQL服務(wù)端的時(shí)候，就不會(huì)將毫秒數(shù)丟棄了，從mysql-connector-java的角度看是修復(fù)了一個(gè)BUG，但是對(duì)于我的應(yīng)用來(lái)說(shuō)卻是觸發(fā)了一個(gè)BUG。

如果你面對(duì)這個(gè)問(wèn)題，你會(huì)怎么修復(fù)呢？

我們當(dāng)時(shí)想了三種方案：

• 將mybatis的Mapper接口中的時(shí)間戳參數(shù)的類型，從java.util.Date改成java.sql.Date；
• 在傳入Mapper接口之前，將傳入的時(shí)間戳按秒取正，代碼如下：

• 在查詢之前，將傳入的時(shí)間戳減1秒；

經(jīng)過(guò)驗(yàn)證，方案1會(huì)，java.util.Date轉(zhuǎn)過(guò)去的java.sql.Date對(duì)象會(huì)將日期之后的精度全部丟掉，從而導(dǎo)致查詢出更多不必要的數(shù)據(jù)；方案3是可以的，就是可能會(huì)查出多一兩條數(shù)據(jù)；方案2也是可以的，相當(dāng)于從代碼上對(duì)mysql-connector-java的特性做了補(bǔ)償。最終我選擇的是方案2。

案例復(fù)現(xiàn)

利用homebrew安裝MySQL，版本是8.0.15，裝好后建一個(gè)表，用來(lái)存放用戶信息，SQL如下：

使用spirngboot + mybatis作為開(kāi)發(fā)框架，定義一個(gè)用戶實(shí)體，代碼如下所示：

定義該實(shí)體對(duì)應(yīng)的Mapper，代碼如下：

設(shè)置連接mysql相關(guān)的配置，代碼如下：

編寫(xiě)測(cè)試代碼，先插入一條數(shù)據(jù)，然后用時(shí)間戳作為查詢條件去查詢，代碼如下：

運(yùn)行單測(cè)，如我們的設(shè)想，確實(shí)是沒(méi)有查詢出數(shù)據(jù)來(lái)，結(jié)果如下：

然后修改代碼，利用上面的代碼將查詢的時(shí)間戳按秒取正，代碼如下：

再次運(yùn)行單測(cè)，如我們的設(shè)想，這次可以查詢出數(shù)據(jù)來(lái)了。

不過(guò)，這里有個(gè)小插曲，我在最開(kāi)始設(shè)計(jì)表的時(shí)候，使用的SQL語(yǔ)句是下面這樣的，

聰明如你一定發(fā)現(xiàn)了，這里的datetime已經(jīng)支持小數(shù)點(diǎn)后更小的時(shí)間精度了，最多支持6位即最多可以支持到微妙級(jí)別。這個(gè)特性是什么時(shí)候引入的呢，我去查閱了 MySQL的官方文檔 ，發(fā)現(xiàn)這個(gè)特性是在mysql 5.6.4之后開(kāi)始支持的。

知識(shí)點(diǎn)總結(jié)

經(jīng)過(guò)了前面的實(shí)際案例分析和案例復(fù)現(xiàn)，想必讀者已經(jīng)對(duì)mysql中DATETIME這個(gè)類型有了一定的認(rèn)識(shí)，接下來(lái)跟我一起看下，我們從這個(gè)案例中可以總結(jié)出哪些經(jīng)驗(yàn)。

1. mysql-connector-java的版本和mysql的版本需要配套使用，例如5.6.4之前的版本，就最好不要使用mysql-connector-java的5.1.23之前的版本，否則就可能會(huì)遇到我們這次遇到的問(wèn)題。

2. MySQL中用來(lái)表示時(shí)間的字段類型有：DATE、DATETIME、TIMESTAMP，它們之間有相同點(diǎn)，各自也有自己的特性，我總結(jié)了一個(gè)表格，如下所示：

3. DATETIME類型在MySQL中是以“YYYYMMDDHHMMSS”格式的整數(shù)存放的，與時(shí)區(qū)無(wú)關(guān)，使用8個(gè)字節(jié)的空間；

4. TIMESTAMP類型可以保存的時(shí)間范圍要小很多，顯示的值依賴時(shí)區(qū)，MySQL的服務(wù)器、操作系統(tǒng)以及客戶端連接都有時(shí)區(qū)的設(shè)置。

5. 一般情況下推薦使用DATETIME作為時(shí)間戳字段，不推薦使用bigint類型來(lái)存儲(chǔ)時(shí)間。

6. 在開(kāi)發(fā)中，應(yīng)該盡量避免使用時(shí)間戳作為查詢條件，如果必須要用，則需要充分考慮MySQL的精度和查詢參數(shù)的精度等問(wèn)題。