MySQL中隱式轉換方法

隱含轉換規則

官方文件中關於隱含轉換的規則如下所描述的：

如果一個或兩個參數為NULL，比較的結果為NULL，除了NULL 安全性 之外。相等比較運算子。對於 NULL ; NULL，結果為真。不需要轉換。如果兩個參數都是整數，它們將作為整數進行比較。

• 如果參數之一是 TIMESTAMP 或 DATETIME 列，另一個參數是常數，在執行比較之前該常數將轉換為時間戳記。這樣做是為了更 ODBC 友善。請注意，這不是針對 IN() 的參數完成的！為了安全起見，在進行比較時始終使用完整的日期時間、日期或時間字串。例如，要在將BETWEEN 與日期或時間值一起使用時獲得最佳結果，請使用CAST() 將值明確轉換為所需的資料類型。被視為常量。例如，如果子查詢傳回要與 DATETIME 值進行比較的整數，則比較將作為兩個整數進行。該整數不會轉換為時間值。若要將運算元作為DATETIME 值進行比較，請使用CAST() 將子查詢值明確轉換為DATETIME。一個參數。如果另一個參數是十進位或整數值，則參數將作為十進位值進行比較；如果另一個參數是浮點值，則參數將作為浮點值進行比較。在所有其他參數中在這種情況下，參數將作為浮點（實數）進行比較。

• 兩個參數至少有一個為 NULL 時，比較的結果也為 NULL，例外是使用 對 NULL 做比較時會傳回兩個 1，這兩種情況都不需要做型別轉換

兩個參數都是字串，會依照字符字串來比較，不做型別轉換

• 
  

• 兩個參數都是整數，依照整數比較，不做型別轉換

• 十六進位的值和非數字做比較時，會被當作二進位編譯器

有一個參數是TIMESTAMP 或DATETIME，另一個參數是常數，常數會轉換為時間戳記

1. 有一個參數是小數型，如果另一個參數是小數或整數，將整數轉換為小數後進行比較，如果另外一個參數是浮點數，則把小數轉換為浮點數進行比較

2. ##所有其他情況下面，兩個參數都會被轉換為浮點再進行比較

3. #問題描述

4. where條件語句裡，字段屬性和賦給的條件，當資料類型不一樣時，這個時候是不能直接比較的，需要進行一致轉換

5. 預設轉換規則是：

不同型別全都轉換成浮點型（下面都說成整數了，一個意思）

6. 如果欄位是字符，條件是整數型，那麼就把表格中欄位全部轉換為整數（上面說的問題，下面有詳細解釋）

7. # 總結

• ##字元轉整數

• 字元開頭的一律為0

數字引用的，直接截取到第一個不是字元的位置

#時間類型轉換

1. #依照字串進行截取

• 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年

• #直接截取時間欄位
• #直接截取日期欄位

• 無意義，直接為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)

以上是MySQL中隱式轉換方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！