作者 | 不剪發(fā)的Tony老師 責(zé)編 | 歐陽姝黎出品 | CSDN博客

??知之為知之，不知為不知，是知也?！墩撜Z》

今天我們來聊聊 MySQL 中存在的隱式數(shù)據(jù)類型轉(zhuǎn)換以及可能帶來的問題。

當(dāng)兩個不同類型的數(shù)據(jù)進行運算時，為了使得它們能夠兼容，MySQL 可能會執(zhí)行隱式的數(shù)據(jù)類型轉(zhuǎn)換。例如，MySQL 在需要時會自動將字符串轉(zhuǎn)換為數(shù)字，反之亦然。

mysql> SELECT 1+'1'; -> 2mysql> SELECT CONCAT(2,' test'); -> '2 test'

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

mysql> SELECT 38.8, CAST(38.8 AS CHAR); -> 38.8, '38.8'mysql> SELECT 38.8, CONCAT(38.8); -> 38.8, '38.8'

以下是比較運算中的類型轉(zhuǎn)換規(guī)則：

如果任意一個參數(shù)為 ，比較的結(jié)果為 ，<=> 相等比較運算符除外。 <=> 的運算結(jié)果為 true，不需要進行類型轉(zhuǎn)換。

如果兩個參數(shù)都是字符串，執(zhí)行字符串比較。

如果兩個參數(shù)都是整數(shù)，執(zhí)行整數(shù)比較。

如果不是和數(shù)字進行比較，十六進制數(shù)值將被看作二進制字符串。

如果一個參數(shù)是 TIMESTAMP 或者 DATETIME 字段，另一個參數(shù)是常量，該常量將會在比較之前轉(zhuǎn)換為時間戳類型。這一規(guī)則是為了更好地支持 ODBC 規(guī)范。IN() 運算符中的參數(shù)不會執(zhí)行這一轉(zhuǎn)換。為了保險起見，記得在執(zhí)行比較運算時使用完整的日期時間、日期或者時間字符串。例如，在使用 BTWEEN 運算符判斷日期或者時間數(shù)據(jù)時，利用 CAST() 函數(shù)將數(shù)據(jù)的類型顯示轉(zhuǎn)換成相應(yīng)的類型。

返回單行結(jié)果的子查詢不會被當(dāng)作常量。例如，當(dāng)一個返回整數(shù)的子查詢和 DATETIME 數(shù)據(jù)進行比較時，DATETIME 將會被轉(zhuǎn)換為整數(shù)類型，而不會將子查詢的結(jié)果轉(zhuǎn)換為時間類型。如果想要執(zhí)行日期時間比較，可以使用 CAST() 函數(shù)顯式將子查詢的結(jié)果轉(zhuǎn)換為 DATETIME 類型。

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

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

關(guān)于時間類型之間的轉(zhuǎn)換規(guī)則，可以參考官方文檔。

以下示例演示了將字符串轉(zhuǎn)換為數(shù)字的比較操作：

mysql> SELECT 1 > '6x'; -> 0mysql> SELECT 7 > '6x'; -> 1mysql> SELECT 0 > 'x6'; -> 0mysql> SELECT 0 = 'x6'; -> 1

如果將字符串類型的字段和數(shù)字進行比較，MySQL 無法使用該字段上的索引快速查找數(shù)據(jù)。例如，str_col 是一個索引字段，該索引無法用于以下語句：

SELECT * FROM tbl_name WHERE str_col=1;

問題的原因在于很多不同的字符串都可以轉(zhuǎn)換為數(shù)字 1，例如’1’、’ 1’ 或者 ‘1a’。

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

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

mysql> SELECT '9223372036854775807' = 9223372036854775807; -> 1mysql> SELECT '9223372036854775807' = 9223372036854775806; -> 1

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

mysql> SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806; -> 0關(guān)于浮點數(shù)比較的更多信息，可以參考官方文檔。

MySQL 服務(wù)器提供了一個轉(zhuǎn)換庫 dtoa，可以支持字符串或者 DECIMAL 數(shù)據(jù)和近似數(shù)字（FLOAT/DOUBLE）之間的基本轉(zhuǎn)換功能：

跨平臺的一致性轉(zhuǎn)換結(jié)果，例如，可以消除 Unix 和 Windows 之間的差異。

可以精確表示之前無法提供足夠精度的數(shù)據(jù)，例如接近 IEEE 限制的數(shù)據(jù)。

以盡可能高的精度將數(shù)字轉(zhuǎn)換成字符串格式。dtoa 的精度總是等于或者高于標(biāo)準(zhǔn) C 代碼庫函數(shù)。

數(shù)字或者時間類型到字符串的隱式轉(zhuǎn)換結(jié)果的字符集和排序規(guī)則取決于 character_set_connection 和 collation_connection 系統(tǒng)變量。（這些變量通常使用 SET NAMES 進行設(shè)置。關(guān)于連接的字符集的信息，可以參考官方文檔。）

這意味著這種轉(zhuǎn)換的結(jié)果是一個非二進制的字符串（CHAR、VARCHAR 或者 LONGTEXT），除非連接字符集被設(shè)置為 binary。此時，轉(zhuǎn)換結(jié)果是一個二進制字符串（BINARY、VARBINARY 或者 LONGBLOB）。

對于整數(shù)類型的表達式，前文所述的表達式求值和表達式賦值有所不同。例如以下語句：

CREATE TABLE t SELECT integer_expr;

這種情況下，表 t 的字段類型取決于整數(shù)表達式的長度，可能是 INT 或者 BIGINT。如果表達式的最大長度超過了 INT，使用 BIGINT 類型。這就意味著我們可以通過一個足夠長的表達式創(chuàng)建 BIGINT 類型的字段：

CREATE TABLE t SELECT 000000000000000000000 AS col;DESC t;Field|Type ||Key|Default|Extra|-----+------+----+---+-------+-----+col |bigint|NO | |0 | |

JSON 數(shù)據(jù)的比較分為兩種情況。第一層次的比較基于被比較數(shù)據(jù)的 JSON 類型，如果兩個類型不同，比較的結(jié)果取決于具有更高優(yōu)先級的類型；如果兩個數(shù)據(jù)的 JSON 類型相同，使用具體的類型規(guī)則進行第二層次的比較。對于 JSON 和非 JSON 數(shù)據(jù)的比較，先將非 JSON 數(shù)據(jù)轉(zhuǎn)換為 JSON 類型，然后進行比較。詳細信息可以參考官方文檔。

作者簡介：不剪發(fā)的 Tony 老師，CSDN 博客專家，CSDN 學(xué)院簽約講師， GitChat 專欄作者。十余年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗。目前在一家全球性的游戲公司從事數(shù)據(jù)庫架構(gòu)設(shè)計和開發(fā)工作，擅長各種數(shù)據(jù)庫管理與 SQL 開發(fā)，擁有Oracle OCP 和 Redhat RHCE 證書。