MySQL 避坑指南之隱式資料類型轉換

知之為知之，不知為不知，是知也。——《論語》

今天我們來聊聊 MySQL 中存在的隱式資料類型轉換以及可能帶來的問題。

當兩個不同類型的資料進行運算時，為了使得它們能夠相容，MySQL 可能會執行隱式的資料類型轉換。例如，MySQL 在需要時會自動將字元串轉換為數字，反之亦然。

我們也可使用 CAST() 函數將數字顯式轉換為字元串。CONCAT() 函數中的隱式類型轉換是因為它只能接收字元串類型的參數。

以下是比較運算中的類型轉換規則：

如果任意一個參數為 NULL，比較的結果為 NULL，<=> 相等比較運算符除外。NULL <=> NULL 的運算結果為 true，不需要進行類型轉換。

如果兩個參數都是字元串，執行字元串比較。

如果兩個參數都是整數，執行整數比較。

如果不是和數字進行比較，十六進位制數值將被看作二進位制字元串。

如果一個參數是 TIMESTAMP 或者 DATETIME 欄位，另一個參數是常量，該常量將會在比較之前轉換為時間戳類型。這一規則是為了更好地支援 ODBC 規範。IN() 運算符中的參數不會執行這一轉換。為了保險起見，記得在執行比較運算時使用完整的日期時間、日期或者時間字元串。例如，在使用 BTWEEN 運算符判斷日期或者時間資料時，利用 CAST() 函數將資料的類型顯示轉換成相應的類型。

返回單行結果的子查詢不會被當作常量。例如，當一個返回整數的子查詢和 DATETIME 資料進行比較時，DATETIME 將會被轉換為整數類型，而不會將子查詢的結果轉換為時間類型。如果想要執行日期時間比較，可以使用 CAST() 函數顯式將子查詢的結果轉換為 DATETIME 類型。

如果一個參數為精確數字類型（decimal），比較的方法取決於另一個參數的類型。如果另一個參數是精確數字或者整數類型，使用精確數字比較；如果另一個參數是浮點數類型，使用浮點數比較。

其他情況下，使用浮點數比較。例如，字元串和精確數字的比較使用浮點數比較方法。

關於時間類型之間的轉換規則，可以參考官方文件。

以下示例演示了將字元串轉換為數字的比較操作：

如果將字元串類型的欄位和數字進行比較，MySQL 無法使用該欄位上的索引快速查詢資料。例如，str_col 是一個索引欄位，該索引無法用於以下語句：

問題的原因在於很多不同的字元串都可以轉換為數字 1，例如’1’、’ 1’ 或者 ‘1a’。

浮點數和 INTEGER 類型的超大數值之間的比較是近似比較，因為整數在比較之前需要轉換為雙精度浮點數，雙精度浮點數無法精確地表示所有的 64 位整數。例如，整數 253 + 1 無法使用浮點數進行表示，只能近似為 253 或者 253 + 2。

舉例來說，以下只有第一個比較運算中的兩個值相等，但是兩個比較運算都返回了 true（1）：

字元串轉換為浮點數與整數轉換為浮點數的方式可能不同。整數可能使用 CPU 轉換為浮點數，而字元串可能使用浮點數乘法進行逐位轉換。另外，轉換結果可能受到各種因素的影響，例如計算機的架構、編譯器版本或者優化級別等。避免這種問題的方法之一就是使用 CAST() 函數，這樣資料就不會被隱式轉換為浮點數。

關於浮點數比較的更多資訊，可以參考官方文件。

MySQL 伺服器提供了一個轉換庫 dtoa，可以支援字元串或者 DECIMAL 資料和近似數字（FLOAT/DOUBLE）之間的基本轉換功能：

跨平臺的一致性轉換結果，例如，可以消除 Unix 和 Windows 之間的差異。

可以精確表示之前無法提供足夠精度的資料，例如接近 IEEE 限制的資料。

以儘可能高的精度將數字轉換成字元串格式。dtoa 的精度總是等於或者高於標準 C 程式碼庫函數。

數字或者時間類型到字元串的隱式轉換結果的字符集和排序規則取決於 character_set_connection 和 collation_connection 系統變數。（這些變數通常使用 SET NAMES 進行設定。關於連線的字符集的資訊，可以參考官方文件。）

這意味著這種轉換的結果是一個非二進位制的字元串（CHAR、VARCHAR 或者 LONGTEXT），除非連線字符集被設定為 binary。此時，轉換結果是一個二進位制字元串（BINARY、VARBINARY 或者 LONGBLOB）。

對於整數類型的表示式，前文所述的表示式求值和表示式賦值有所不同。例如以下語句：

這種情況下，表 t 的欄位類型取決於整數表示式的長度，可能是 INT 或者 BIGINT。如果表示式的最大長度超過了 INT，使用 BIGINT 類型。這就意味著我們可以通過一個足夠長的表示式創建 BIGINT 類型的欄位：

JSON 資料的比較分為兩種情況。第一層次的比較基於被比較資料的 JSON 類型，如果兩個類型不同，比較的結果取決於具有更高優先順序的類型；如果兩個資料的 JSON 類型相同，使用具體的類型規則進行第二層次的比較。對於 JSON 和非 JSON 資料的比較，先將非 JSON 資料轉換為 JSON 類型，然後進行比較。詳細資訊可以參考官方文件。

作者簡介：不剪髮的 Tony 老師，CSDN 部落格專家，CSDN 學院簽約講師， GitChat 專欄作者。十餘年資料庫管理與開發經驗。目前在一家全球性的遊戲公司從事資料庫架構設計和開發工作，擅長各種資料庫管理與 SQL 開發，擁有Oracle OCP 和 Redhat RHCE 證書。

因一個計算機故障而「停工」！觀測宇宙 30 多年的哈勃太空望遠鏡還能堅持多久？
iPhone13 系列售價曝光：沒有漲價；曝華為P50系列7月29日釋出；丁磊稱專業比學校更重要：你同意嗎？|極客頭條
Stack Overflow上最火的一段程式碼竟然有 Bug