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

??知之為知之，不知為不知，是知也?！墩撜Z(yǔ)》

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

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

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

我們也可使用 CAST() 函數(shù)將數(shù)字顯式轉(zhuǎn)換為字符串。CONCAT() 函數(shù)中的隱式類型轉(zhuǎn)換是因?yà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'

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT * FROM tbl_name WHERE str_col=1;

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

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

舉例來(lái)說(shuō)，以下只有第一個(gè)比較運(yùn)算中的兩個(gè)值相等，但是兩個(gè)比較運(yùn)算都返回了 true（1）：

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

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

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

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

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

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

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

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

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

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

CREATE TABLE t SELECT integer_expr;

這種情況下，表 t 的字段類型取決于整數(shù)表達(dá)式的長(zhǎng)度，可能是 INT 或者 BIGINT。如果表達(dá)式的最大長(zhǎng)度超過(guò)了 INT，使用 BIGINT 類型。這就意味著我們可以通過(guò)一個(gè)足夠長(zhǎng)的表達(dá)式創(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 類型，如果兩個(gè)類型不同，比較的結(jié)果取決于具有更高優(yōu)先級(jí)的類型；如果兩個(gè)數(shù)據(jù)的 JSON 類型相同，使用具體的類型規(guī)則進(jìn)行第二層次的比較。對(duì)于 JSON 和非 JSON 數(shù)據(jù)的比較，先將非 JSON 數(shù)據(jù)轉(zhuǎn)換為 JSON 類型，然后進(jìn)行比較。詳細(xì)信息可以參考官方文檔。

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