MySQL之隱式轉換

来源:http://www.cnblogs.com/liuzhuqing/archive/2017/10/09/7641542.html
-Advertisement-
Play Games

隱式轉化規則 官方文檔中關於隱式轉化的規則是如下描述的： If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison ...


 

隱式轉化規則

官方文檔中關於隱式轉化的規則是如下描述的：

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.

翻譯為中文就是：

    1. 兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉換
    2. 兩個參數都是字元串，會按照字元串來比較，不做類型轉換
    3. 兩個參數都是整數，按照整數來比較，不做類型轉換
    4. 十六進位的值和非數字做比較時，會被當做二進位串
    5. 有一個參數是 TIMESTAMP 或 DATETIME，並且另外一個參數是常量，常量會被轉換為 timestamp
    6. 有一個參數是 decimal 類型，如果另外一個參數是 decimal 或者整數，會將整數轉換為 decimal 後進行比較，如果另外一個參數是浮點數，則會把 decimal 轉換為浮點數進行比較
    7. 所有其他情況下，兩個參數都會被轉換為浮點數再進行比較

 

問題描述

  • where 條件語句里，欄位屬性和賦給的條件，當數據類型不一樣，這時候是沒法直接比較的，需要進行一致轉換
  • 預設轉換規則是：
    • 不同類型全都轉換為浮點型（下文都說成整型了，一個意思）
    • 如果欄位是字元，條件是整型，那麼會把表中欄位全都轉換為整型（也就是上面圖中的問題，下麵有詳細解釋）

轉換總結

  1. 字元轉整型
    • 字元開頭的一律為0
    • 數字開頭的，直接截取到第一個不是字元的位置
  2. 時間類型轉換
    • date 轉 datetime 或者 timestamp
      • 追加 00:00:00
    • date 轉 time
      • 無意義，直接為 00:00:00
    • datetime 或者 timestamp 轉 date
      • 直接截取date欄位
    • datetime 或者 timestamp 轉 time
      • 直接截取time欄位
    • time 轉 datetime 或者 timestamp
      • 按照字元串進行截取
      • 23:12:13 -> 2023-12-13（這個後文有討論）
        • cast函數只能轉datetime，不能轉timestamp
        • 如果按照timestamp來理解，因為timestamp是有範圍的('1970-01-01 00:00:01.000000' to'2038-01-19 03:14:07.999999')，所以只能是2023年，而不能是1923年
      • 對於不符合的時間值，如10:12:32等，會變為 0000-00-00 或為 空
    • 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=latin1
1 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)


作者：JackpGao
鏈接：http://www.jianshu.com/p/6f34e9708a80
來源：簡書
著作權歸作者所有

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SQL SERVER 使用ESCAPE轉義
    使用SQL語句查詢是開發中常做的事，在實際的情況中，可能會遇到，查詢包含某個關鍵詞的所有行，而這個關鍵詞正好也是SQL SERVER的關鍵詞。 下麵有一張表Member，有如下的數據： 假如需要查詢名字中包含%字元的數據，首先我們會有這樣的查詢語句： SELECT * FROM dbo.Member ...
  • Oracle03、用戶授權以及用戶解鎖問題
    一、關於Oracle用戶許可權表的認知 在Oracle中有很多的用戶，不同的用戶有著不同的許可權，在學習階段，一般使用的是scott用戶，並且對該用戶的表進行增刪改查等操作。下麵就簡單介紹一下常見的用戶。 sys;//系統管理員，擁有Oracle的最高的許可權； system;//本地管理員，擁有次高的權 ...
  • Redis “瘦身”指南
    隨著業務發展和數據積累，你的 Redis 伺服器變得越來越臃腫了，甚至記憶體爆滿影響了業務，那麼它是如何一步一步走到這個地步，又如何解決呢？希望本文能幫助到你。 ...
  • DAO模式
    JDBC封裝 優點: 附註: DAO模式提供了訪問關係型數據系統所需操作的介面,將數據訪問和業務邏輯分開，對 上層提供面向對象的數據訪問介面. DAO模式實現兩層分離: 代碼間分工明確，數據訪問層代碼不影響業務邏輯層代碼，這也符合單一職能原則，降低了耦合度，提高了代碼的可復用性。。 隔離了不同的數據 ...
  • 關於MySQL建立新用戶並授權
    創建用戶的語法： Create user `username`@host [Identified by [password] `password`]; 例： create user `teacher`@`localhost` identified by ‘12345’, 或者 create user ...
  • SQL Server學習之路（六）：“增刪改查”之“查”
    0.目錄 1. "前言" 2. "最基本的SQL查詢語句" 3. "select...from..." 3.1 "“\ ”與“Top num \ ”" 3.2 "查詢指定列" 3.3 "Isnull函數：判斷空值" 3.4 "使用"+"將"列"與"字元串"連接起來（使用as重命名）" 4. "ord ...
  • Oracle 常用內置系統函數總結
    Oracle資料庫 內置系統函數主要分為以下類別：數學函數、字元串函數、日期函數、轉換函數、聚集函數、分析/聚合函數 一、數學函數 返回數字 abs(n)：返回數字n的絕對值 ceil(n)：返回>=數字n的最小整數 floor(n)：返回<=數字n的最大整數 round(n,[m])：做四捨五入運 ...
  • MySQL參數log_bin_trust_function_creators介紹
    MySQL的有個參數log_bin_trust_function_creators，官方文檔對這個參數的介紹、解釋如下所示： log_bin_trust_function_creators Command-Line Format --log-bin-trust-function-creators S... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類