隱式轉化規則
官方文檔中關于隱式轉化的規則是如下描述的：
If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe equality comparison operator. For NULL NULL, the result is true. No conversion is needed.
-
If both arguments in a comparison operation are strings, they are compared as strings.
-
If both arguments are integers, they are compared as integers.
-
Hexadecimal values are treated as binary strings if not compared to a number.
-
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME. -
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
-
In all other cases, the arguments are compared as floating-point (real) numbers.
翻譯為中文就是：
-
兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉換
-
兩個參數都是字符串，會按照字符串來比較，不做類型轉換
-
兩個參數都是整數，按照整數來比較，不做類型轉換
-
十六進制的值和非數字做比較時，會被當做二進制串
-
有一個參數是 TIMESTAMP 或 DATETIME，并且另外一個參數是常量，常量會被轉換為 timestamp
-
有一個參數是 decimal 類型，如果另外一個參數是 decimal 或者整數，會將整數轉換為 decimal 后進行比較，如果另外一個參數是浮點數，則會把 decimal 轉換為浮點數進行比較
-
所有其他情況下，兩個參數都會被轉換為浮點數再進行比較
問題描述
-
where 條件語句里，字段屬性和賦給的條件，當數據類型不一樣，這時候是沒法直接比較的，需要進行一致轉換
-
默認轉換規則是：
-
不同類型全都轉換為浮點型（下文都說成整型了，一個意思）
-
如果字段是字符，條件是整型，那么會把表中字段全都轉換為整型（也就是上面圖中的問題，下面有詳細解釋）
轉換總結
-
字符轉整型
-
字符開頭的一律為0
-
數字開頭的，直接截取到第一個不是字符的位置
時間類型轉換
-
按照字符串進行截取
-
23:12:13 -> 2023-12-13（這個后文有討論）
-
對于不符合的時間值，如10:12:32等，會變為 0000-00-00 或為 空
-
cast函數只能轉datetime，不能轉timestamp
-
如果按照timestamp來理解，因為timestamp是有范圍的(‘1970-01-01 00:00:01.000000′ to’2038-01-19 03:14:07.999999’)，所以只能是2023年，而不能是1923年
-
直接截取time字段
-
直接截取date字段
-
無意義，直接為 00:00:00
-
追加 00:00:00
-
date 轉 datetime 或者 timestamp
-
date 轉 time
-
datetime 或者 timestamp 轉 date
-
datetime 或者 timestamp 轉 time
-
time 轉 datetime 或者 timestamp
-
time和datetime轉換為數字時，會變為雙精度，加上ms（版本不同不一樣）
案例分析
-
表結構，name字段有索引
--?注意name字段是有索引的CREATE?TABLE?`t3`?(??`id`?int(11)?NOT?NULL,??`c1`?int(11)?NOT?NULL,??`name`?varchar(100)?NOT?NULL?DEFAULT?'fajlfjalfka',??KEY?`name`?(`name`),??KEY?`id`?(`id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=latin11?row?in?set?(0.00?sec)
--?模擬線上一個隱式轉換帶來的全表掃面慢查詢--?發生隱式轉換 xxxx.test>?select?*?from?t3?where?name?=?0; +----+----+-------------+ |?id?|?c1?|?name????????| +----+----+-------------+ |??1?|??2?|?fajlfjalfka?| |??2?|??0?|?fajlfjalfka?| |??1?|??2?|?fajlfjalfka?| |??2?|??0?|?fajlfjalfka?| +----+----+-------------+ 4?rows?in?set,?4?warnings?(0.00?sec)--?上述SQL執行計劃是全表掃描，掃描后，字符轉整型，都是0，匹配上了條件，全部返回 xxxx.test>?desc?select?*?from?t3?where?name?=?0; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra???????| +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |??1?|?SIMPLE??????|?t3????|?ALL??|?name??????????|?NULL?|?NULL????|?NULL?|????4?|?Using?where?| +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1?row?in?set?(0.00?sec)--?加上單引號后，是走name索引的，非全表掃描 xxxx.test>?desc?select?*?from?t3?where?name?=?'0'; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key??|?key_len?|?ref???|?rows?|?Extra?????????????????| +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ |??1?|?SIMPLE??????|?t3????|?ref??|?name??????????|?name?|?102?????|?const?|????1?|?Using?index?condition?| +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ 1?row?in?set?(0.00?sec)--?走索引，沒返回 xxxx.test>??select?*?from?t3?where?name?=?'1'; Empty?set?(0.00?sec)
解釋
-
如果條件寫0或者1，會進行全表掃面，需要把所有的name字段由字符全都轉換為整型，再和0或者1去比較。由于都是字母開頭的字符，會全都轉為為0，返回的結果就是所有行。
-
那有人問了，為什么不把條件里的 0 自動改成 ‘0’ ？見下文。
轉換舉例
--?字符開頭，直接是0 xxxx.test>?select?cast('a1'?as?unsigned?int)?as?test?; +------+ |?test?| +------+ |????0?| +------+ 1?row?in?set,?1?warning?(0.00?sec) xxxx.test>?show?warnings; +---------+------+-----------------------------------------+ |?Level???|?Code?|?Message?????????????????????????????????| +---------+------+-----------------------------------------+ |?Warning?|?1292?|?Truncated?incorrect?INTEGER?value:?'a1'?| +---------+------+-----------------------------------------+ 1?row?in?set?(0.00?sec)--?開頭不是字符，一直截取到第一個不是字符的位置 xxxx.test>?select?cast('1a1'?as?unsigned?int)?as?test?;? +------+ |?test?| +------+ |????1?| +------+ 1?row?in?set,?1?warning?(0.00?sec) xxxx.test>?select?cast('123a1'?as?unsigned?int)?as?test?; +------+ |?test?| +------+ |??123?| +------+ 1?row?in?set,?1?warning?(0.00?sec)--?直接按照字符截取，補上了20（不能補19） xxxx.test>?select?cast('23:12:13'?as?datetime)?as?test?; +---------------------+ |?test????????????????| +---------------------+ |?2023-12-13?00:00:00?| +---------------------+ 1?row?in?set?(0.00?sec)--?為什么不能轉換為timestamp，沒搞清楚，官方文檔給的轉換類型里沒有timestamp。如果是這樣的話，上面的datetime就不好解釋為什不是1923了。難道是檢測了當前的系統時間？ xxxx.test>?select?cast('23:12:13'?as?timestamp)?as?test?;???? ERROR?1064?(42000):?You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?mysql?server?version?for?the?right?syntax?to?use?near?'timestamp)?as?test'?at?line?1--?這個時間無法轉換成datetime xxxx.test>?select?cast('10:12:32'?as?datetime)?as?test?;????????? +------+ |?test?| +------+ |?NULL?| +------+ 1?row?in?set,?1?warning?(0.00?sec) xxxx.test>?show?warnings?; +---------+------+--------------------------------------+ |?Level???|?Code?|?Message??????????????????????????????| +---------+------+--------------------------------------+ |?Warning?|?1292?|?Incorrect?datetime?value:?'10:12:32'?| +---------+------+--------------------------------------+ 1?row?in?set?(0.00?sec)--?5.5版本下，時間轉字符，會增加ms xxxx.(none)>?select?version(); +------------+ |?version()??| +------------+ |?5.5.31-log?| +------------+ 1?row?in?set?(0.00?sec) xxxx.(none)>?select?CURTIME(),?CURTIME()+0,?NOW(),?NOW()+0?; +-----------+---------------+---------------------+-----------------------+ |?CURTIME()?|?CURTIME()+0???|?NOW()???????????????|?NOW()+0???????????????| +-----------+---------------+---------------------+-----------------------+ |?15:40:01??|?154001.000000?|?2016-05-06?15:40:01?|?20160506154001.000000?| +-----------+---------------+---------------------+-----------------------+ 1?row?in?set?(0.00?sec)--?5.6?不會 xxxx.test>?select?version(); +------------+ |?version()??| +------------+ |?5.6.24-log?| +------------+ 1?row?in?set?(0.00?sec) xxxx.test>?select?CURTIME(),?CURTIME()+0,?NOW(),?NOW()+0?; +-----------+-------------+---------------------+----------------+ |?CURTIME()?|?CURTIME()+0?|?NOW()???????????????|?NOW()+0????????| +-----------+-------------+---------------------+----------------+ |?15:40:55??|??????154055?|?2016-05-06?15:40:55?|?20160506154055?| +-----------+-------------+---------------------+----------------+ 1?row?in?set?(0.00?sec)
為什么不把 where name = 0 中的 0 轉換為 ‘0’ ？
-
如果是數字往字符去轉換，如 0 轉’0’，這樣查詢出來的結果只能是字段等于 ‘0’，而實際上，表里的數據，如’a0’，’00’，這其實都是用戶想要的0，畢竟是用戶指定了數字0，所以MySQL還是以用戶發出的需求為準，否則，’00’這些都不會返回給用戶。
總結
-
有了上面的內容，開頭的問題是可以解釋了。
-
上圖的例子，是不是可以用來繞過身份驗證？
補充
--?上面遺留的問題，跟系統時間并沒有關系。懷疑雖然指定的是datetime，但是內部還是按照timestamp去做的。 mysql>?select?now(); +---------------------+ |?now()???????????????| +---------------------+ |?1999-08-03?14:16:50?| +---------------------+ 1?row?in?set?(0.00?sec) mysql>?select?cast('23:12:13'?as?datetime)?as?test?; +---------------------+ |?test????????????????| +---------------------+ |?2023-12-13?00:00:00?| +---------------------+ 1?row?in?set?(0.00?sec)
