切換語言為:簡體

SQL中隱式轉換 where 值=0 引發的問題

  • 爱糖宝
  • 2024-09-13
  • 2056
  • 0
  • 0

一.問題重現

1.問題模擬

🌰舉例

一個person表,假設其中有sex性別欄位型別為varchar,0代表女,1代表男,建表如下

SQL中隱式轉換 where 值=0 引發的問題

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `name` varchar(255) NOT NULL COMMENT '名稱',
  `age` int(10) NOT NULL COMMENT '年齡',
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性別(男;女)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入模擬資料

SQL中隱式轉換 where 值=0 引發的問題

INSERT INTO `demo`.`person` (`id`, `name`, `age`, `sex`) VALUES (1, '張三', 20, '男');
INSERT INTO `demo`.`person` (`id`, `name`, `age`, `sex`) VALUES (2, '李四', 30, '女');
INSERT INTO `demo`.`person` (`id`, `name`, `age`, `sex`) VALUES (3, '小姐姐', 18, '0');
INSERT INTO `demo`.`person` (`id`, `name`, `age`, `sex`) VALUES (4, '王五', 20, '男');

2.查詢語句

現在我們要查詢性別為0的問題資料

SQL中隱式轉換 where 值=0 引發的問題

SELECT * FROM person WHERE sex = 0;

執行結果

SQL中隱式轉換 where 值=0 引發的問題 

結果是不是出人意料,竟然將所有資料都查出,是不是失效出bug了?

其實這就是MySQL的隱式特性,而不是什麼bug,別急後面慢慢詳解...


二.MySQL隱式轉換

1.隱式轉換是什麼?

MySQL中的隱式型別轉換是指當執行某些操作時,如比較操作或其他需要特定資料型別引數的操作時,如果參與操作的表示式或列的資料型別不匹配,MySQL將會自動進行資料型別轉換以適應預期的資料型別。

👽人話解釋

就是欄位型別不匹配,mysql會自動轉,如果你以數字0為條件,mysql就會將欄位內容轉成數字進行處理

2.示例問題分析

在MySQL中,當你嘗試用整數 0 作為條件去查詢一個 VARCHAR 型別的欄位時,MySQL 會嘗試將 VARCHAR 欄位的內容轉換為數字進行比較

這種情況下,MySQL 會檢查字串的開始部分是否可以解析為一個數字。如果可以,它將使用這個數字值進行比較;如果不能解析為數字,則預設使用 0 進行比較。

也就是說

SELECT * FROM person WHERE sex = 0; 和 SELECT * FROM person WHERE sex = '0';

兩個表達的意思完全不一樣,用0會查到所有非數字開頭的字串,用其他數字查則會匹配到與之對應數字開頭的字串

SQL中隱式轉換 where 值=0 引發的問題

SQL中隱式轉換 where 值=0 引發的問題

總結

1.問題重現:

  • 建立 person 表,sex 欄位為 VARCHAR 型別。

  • 插入資料,包括 sex 為 '男'、'女'、'0' 等。

  • 查詢 SELECT * FROM person WHERE sex = 0; 返回所有資料,而非僅 '0'。

2.原因分析:

  • MySQL 嘗試將 VARCHAR 欄位內容轉換為數字進行比較。

  • 如果字串可以解析為數字,則使用該數字;否則預設使用 0。

3.解決方法:

  • 顯式型別轉換:SELECT * FROM person WHERE CAST(sex AS SIGNED) = 0;

  • 直接使用字串比較:SELECT * FROM person WHERE sex = '0';

4.結論

  • 隱式轉換會導致意外結果:使用整數 0 查詢 VARCHAR 欄位時,MySQL 會嘗試將字串轉換為數字。

  • 顯式型別轉換更安全:顯式地進行型別轉換可以確保查詢結果符合預期。

0則評論

您的電子郵件等資訊不會被公開,以下所有項目均必填

OK! You can skip this field.