一個 MySQL 隱式轉換的坑,差點把伺服器整崩潰了

来源:https://www.cnblogs.com/fengzheng/archive/2022/11/10/16876158.html
-Advertisement-
Play Games

我是風箏,公眾號「古時的風箏」,專註於 Java技術 及周邊生態。 文章會收錄在 JavaNewBee 中,更有 Java 後端知識圖譜,從小白到大牛要走的路都在裡面。 本來是一個平靜而美好的下午,其他部門的同事要一份數據報表臨時彙報使用,因為系統目前沒有這個維度的功能,所以需要寫個SQL馬上出一下 ...


我是風箏,公眾號「古時的風箏」,專註於 Java技術 及周邊生態。
文章會收錄在 JavaNewBee 中,更有 Java 後端知識圖譜,從小白到大牛要走的路都在裡面。

本來是一個平靜而美好的下午,其他部門的同事要一份數據報表臨時彙報使用,因為系統目前沒有這個維度的功能,所以需要寫個SQL馬上出一下,一個同事接到這個任務,於是開始在測試環境拼裝這條 SQL,剛過了幾分鐘,同事已經自信的寫好了這條SQL,於是拿給DBA,到線上跑一下,用客戶端工具導出Excel 就好了,畢竟是臨時方案嘛。

就在SQL執行了之後,意外發生了,先是等了一下,發現還沒執行成功,猜測可能是數據量大的原因,但是隨著時間滴滴答答流逝,逐漸意識到情況不對了,一看監控,CPU已經上去了,但是線上數據量雖然不小,也不至於跑成這樣吧,眼看著要跑死了,趕緊把這個事務結束掉了。

什麼原因呢?查詢的條件和 join 連接的欄位基本都有索引,按道理不應該這樣啊,於是趕緊把SQL拿下來,也沒看出什麼問題,於是限制查詢條數再跑了一次,很快出結果了,但是結果卻大跌眼鏡,出來的查詢結果並不是預期的。

我到底做錯了什麼

經過一番檢查之後,最終發現了問題所在,是 join 連接中有一個欄位寫錯了,因為這兩個欄位有一部分名稱是相同的,於是智能的 SQL 客戶端給出了提示,順手就給敲上去了。但是接下來,更讓人迷惑了,因為要連接的欄位是 int 類型,而寫錯的這個欄位是 varchar 類型,難道不應該報錯嗎?怎麼還能正常執行,並且還有預期外的查詢結果?

難道是 MySQL 有 bug 了,必須要研究一下了。

復現當時的情景

假設有兩張表,這兩張表的結構和數據是下麵這樣的。

第一張 user表。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `user` VALUES (1, '張三', 28, '2022-09-06 07:40:56', '2022-09-06 07:40:59');

image-20220906074916227

第二張 order

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `order_code` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `money` decimal(20,0) DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `order` VALUES (1, 2, '1d90530e-6ada-47c1-b2fa-adba4545aabd', 100, 'xxx購買兩件商品', '2022-09-06 07:42:25', '2022-09-06 07:42:27');

image-20220906075142861

目的是查看所有用戶的 order 記錄,假設數據量比較少,可以直接查,不考慮性能問題。

本來的 SQL 語句應該是這樣子的,查詢 order表中用戶iduser_iduser表的記錄。

select o.* from `user` u 
left JOIN `order` o on u.id = o.user_id;

但是呢,因為手抖,將 on 後面的條件寫成了 u.id = o.order_code,完全關聯錯誤,這兩個欄位完全沒有聯繫,而且u.id是 int 類型,o.order_codevarchar類型。

select o.* from `user` u 
left JOIN `order` o on u.id = o.order_code;

這樣的話, 當我們執行這條語句的時候,會不會查出數據來呢?

我的第一感覺是,不僅不會查出數據,而且還會報錯,因為連接的這兩個欄位類型都不一樣,值更不一樣。

結果卻被啪啪打臉,不僅沒有報錯,而且還查出了數據。

image-20221101174113767

可以把這個問題簡化一下,簡化成下麵這條語句,同樣也會出現問題。

select * from `order` where order_code = 1;

image-20221101210742113

明明這條記錄的 order_code 欄位的值是 1d90530e-6ada-47c1-b2fa-adba4545aabd,怎麼用 order_code=1的條件就把它給查出來了。

根源所在

相信有的同學已經猜出來了,這裡是 MySQL 進行了隱式轉換,由於查詢條件後面跟的查詢值是整型的,所以 MySQL 將 order_code欄位進行了字元串到整數類型的轉換,而轉換後的結果正好是 1

通過 cast函數轉換驗證一下結果。

select cast('1d90530e-6ada-47c1-b2fa-adba4545aabd' as unsigned);

image-20221101212111341

再用兩條 SQL 看一下字元串到整數類型轉換的規則。

select cast('223kkk' as unsigned);
select cast('k223kkk' as unsigned);

image-20221101212300554

223kkk轉換後的結果是 223,而k223kkk轉換後的結果是0。總結一下,轉換的規則是:

1、從字元串的左側開始向右轉換,遇到非數字就停止;

2、如果第一個就是非數字,最後的結果就是0;

隱式轉換的規則

當操作符與不同類型的操作數一起使用的時候,就會發生隱式轉換。

例如算數運算符的前後是不同類型時,會將非數字類型轉換為數字,比如 '5a'+2,就會將5a轉換為數字類型,然後和2相加,最後的結果就是 7 。

image-20221101213326375

再比如 concat函數是連接兩個字元串的,當此函數的參數出現非字元串類型時,就會將其轉換為字元串,例如concat(88,'就是發'),最後的結果就是 88就是發

image-20221101213611080

MySQL 官方文檔有以下幾條關於隱式轉換的規則:

1、兩個參數至少有一個是 NULL 時,比較的結果也是 NULL,例外是使用 <=> 對兩個 NULL 做比較時會返回 1,這兩種情況都不需要做類型轉換;

也就是兩個參數中如果只有一個是NULL,則不管怎麼比較結果都是 NULL,而兩個 NULL 的值不管是判斷大於、小於或等於,其結果都是1。

2、兩個參數都是字元串,會按照字元串來比較,不做類型轉換;

3、兩個參數都是整數,按照整數來比較,不做類型轉換;

4、十六進位的值和非數字做比較時,會被當做二進位字元串;

例如下麵這條語句,查詢 user 表中name欄位是 0x61 的記錄,0x是16進位寫法,其對應的字元串是英文的 'a',也就是它對應的 ASCII 碼。

select * from user where name = 0x61;

所以,上面這條語句其實等同於下麵這條

select * from user where name = 'a';

可以用 select 0x61;驗證一下。

5、有一個參數是 TIMESTAMP 或 DATETIME,並且另外一個參數是常量,常量會被轉換為 時間戳;

例如下麵這兩條SQL,都是將條件後面的值轉換為時間戳再比較了,只不過

image-20221102100731306

6、有一個參數是 decimal 類型,如果另外一個參數是 decimal 或者整數,會將整數轉換為 decimal 後進行比較,如果另外一個參數是浮點數(一般預設是 double),則會把 decimal 轉換為浮點數進行比較;

在不同的數值類型之間,總是會向精度要求更高的那一個類型轉換,但是有一點要註意,在MySQL 中浮點數的精度只有53 bit,超過53bit之後的話,如果後面1位是1就進位,如果是0就直接捨棄。所以超大浮點數在比較的時候其實只是取的近似值。

7、所有其他情況下,兩個參數都會被轉換為浮點數再進行比較;

如果不符合上面6點規則,則統一轉成浮點數再進行運算

避免進行隱式轉換

我們在平時的開發過程中,儘量要避免隱式轉換,因為一旦發生隱式轉換除了會降低性能外, 還有很大可能會出現不期望的結果,就像我最開始遇到的那個問題一樣。

之所以性能會降低,還有一個原因就是讓本來有的索引失效。

select * from `order` where order_code = 1;

order_code 是 varchar 類型,假設我已經在 order_code 上建立了索引,如果是用“=”做查詢條件的話,應該直接命中索引才對,查詢速度會很快。但是,當查詢條件後面的值類型不是 varchar,而是數值類型的話,MySQL 首先要對 order_code 欄位做類型轉換,轉換為數值類型,這時候,之前建的索引也就不會命中,只能走全表掃描,查詢性能指數級下降,搞不好,資料庫直接查崩了。


這位英俊瀟灑的少年,如果覺得還不錯的話,給個推薦可好!

公眾號「古時的風箏」,Java 開發者,全棧工程師,bug 殺手,擅長解決問題。
一個兼具深度與廣度的程式員鼓勵師,本打算寫詩卻寫起了代碼的田園碼農!堅持原創乾貨輸出,你可選擇現在就關註我,或者看看歷史文章再關註也不遲。長按二維碼關註,跟我一起變優秀!

人生沒有回頭路,珍惜當下。
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 2022年11月8日.NET 7正式發佈 .NET仍然是最快、最受歡迎、最值得信賴的平臺之一,其龐大的.NET軟體包生態系統包括33萬多個軟體包。 .NET 7為您的應用程式帶來了更高的性能和C# 11/F# 7、.NET MAUI、ASP.NET Core/Blazor、Web APIs、WinF ...
  • 一:背景 1.講故事 有朋友咨詢個問題,他每次在調試 WinDbg 的時候,進程初始化斷點之前都會有一些 dll 載入到進程中,比如下麵這樣: Microsoft (R) Windows Debugger Version 10.0.25200.1003 X86 Copyright (c) Micro ...
  • 依賴管理是 NuGet 的核心功能。Nuget管理單個項目的依賴關係很容易。管理多項目解決方案的依賴關係可能會變得很困難,因為它們的規模和複雜性開始擴大。 在您管理許多不同項目的公共依賴項的情況下,您可以利用 NuGet 的中央包管理功能從一個位置輕鬆完成所有這些工作。 要開始使用中央包管理,您可以 ...
  • 前言 之前想用Markdown來寫框架文檔,找來找去發現還是Jekyll的多,但又感覺不是很合我的需求 於是打算自己簡單弄一個展示Markdown文檔的網站工具,要支持多版本、多語言、導航、頁內導航等,並且支持Github Pages免費站點 組件選擇 我自己呢比較喜歡C#,恰好現在ASP.Net ...
  • Part One: System call tracing 任務:修改Xv6內核的代碼來列印出每個系統調用以及返回值 根據提示,應該在syscall.c中修改syscall()。 首先在syscall.c中增加對應系統調用名稱的數組: static char *syscalls_name[] = { ...
  • 前幾日考慮給ubuntu安裝圖形桌面驅動。第一次安裝是在圖形界面下啟動終端軟體跑的NVIDIA給的安裝腳本,結果它跑了一半自動重啟了圖形界面,導致腳本被退出了。測試發現顯卡確實已被驅動,但由於擔心會不會有某些組件缺失,便退出到tty完完整整重新又跑了遍腳本。結果這一下一弄,重啟後再次啟動則在引導完成 ...
  • Arnold for Cinema 4D是用於C4D R25的阿諾德渲染器,支持R25.010 及以上的C4D,SolidAngle Cinema 4D To Arnold 是一款先進的蒙特卡洛光線追蹤渲染器,專為動畫長度和視覺效果的需求而打造。 詳情:C4D R25插件:Arnold for Ci ...
  • 一、什麼是 SQLite 資料庫 SQLite 是嵌入式SQL資料庫引擎。與大多數其他 SQL 資料庫不同,SQLite 沒有單獨的伺服器進程。SQLite 直接讀取和寫入普通磁碟文件。具有多個表,索引,觸發器和視圖的完整 SQL 資料庫包含在單個磁碟文件中。資料庫文件格式是跨平臺的-您可以在32位 ...
一周排行
    -Advertisement-
    Play Games
  • 隨著Aspire發佈preview5的發佈,Microsoft.Extensions.ServiceDiscovery隨之更新, 服務註冊發現這個屬於老掉牙的話題解決什麼問題就不贅述了,這裡主要講講Microsoft.Extensions.ServiceDiscovery(preview5)以及如何 ...
  • 概述:通過使用`SemaphoreSlim`,可以簡單而有效地限制非同步HTTP請求的併發量,確保在任何給定時間內不超過20個網頁同時下載。`ParallelOptions`不適用於非同步操作,但可考慮使用`Parallel.ForEach`,儘管在非同步場景中謹慎使用。 對於併發非同步 I/O 操作的數量 ...
  • 1.Linux上安裝Docken 伺服器系統版本以及內核版本:cat /etc/redhat-release 查看伺服器內核版本:uname -r 安裝依賴包:yum install -y yum-utils device-mapper-persistent-data lvm2 設置阿裡雲鏡像源:y ...
  • 概述:WPF界面綁定和渲染大量數據可能導致性能問題。通過啟用UI虛擬化、非同步載入和數據分頁,可以有效提高界面響應性能。以下是簡單示例演示這些優化方法。 在WPF中,當你嘗試綁定和渲染大量的數據項時,性能問題可能出現。以下是一些可能導致性能慢的原因以及優化方法: UI 虛擬化: WPF提供了虛擬化技術 ...
  • 引言 上一章節介紹了 TDD 的三大法則,今天我們講一下在單元測試中模擬對象的使用。 Fake Fake - Fake 是一個通用術語,可用於描述 stub或 mock 對象。 它是 stub 還是 mock 取決於使用它的上下文。 也就是說,Fake 可以是 stub 或 mock Mock - ...
  • 為.net6在CentOS7上面做準備,先在vmware虛擬機安裝CentOS 7.9 新建CentOS764位的系統 因為CentOS8不更新了,所以安裝7;簡單就一筆帶過了 選擇下載好的操作系統的iso文件,下載地址https://mirrors.aliyun.com/centos/7.9.20 ...
  • 經過前面幾篇的學習,我們瞭解到指令的大概分類,如:參數載入指令,該載入指令以 Ld 開頭,將參數載入到棧中,以便於後續執行操作命令。參數存儲指令,其指令以 St 開頭,將棧中的數據,存儲到指定的變數中,以方便後續使用。創建實例指令,其指令以 New 開頭,用於在運行時動態生成並初始化對象。方法調用指... ...
  • LiteDB 是一個輕量級的嵌入式 NoSQL 資料庫,其設計理念與 MongoDB 類似,但它是完全使用 C# 開發的,因此與 C# 應用程式的集成非常順暢。與 SQLite 相比,LiteDB 提供了 NoSQL(即鍵值對)的數據存儲方式,並且是一個開源且免費的項目。它適用於桌面、移動以及 We ...
  • 1 開源解析和拆分文檔 第三方的工具去對文件解析拆分,去將我們的文件內容給提取出來,並將我們的文檔內容去拆分成一個小的chunk。常見的PDF word mark down, JSON、HTML。都可以有很好的一些模塊去把這些文件去進行一個東西去提取。 優勢 支持豐富的文檔類型 每種文檔多樣化選擇 ...
  • OOM是什麼?英文全稱為 OutOfMemoryError(記憶體溢出錯誤)。當程式發生OOM時,如何去定位導致異常的代碼還是挺麻煩的。 要檢查OOM發生的原因,首先需要瞭解各種OOM情況下會報的異常信息。這樣能縮小排查範圍,再結合異常堆棧、heapDump文件、JVM分析工具和業務代碼來判斷具體是哪 ...