程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL的隱式類型轉換整理總結，mysql整理總結

MySQL的隱式類型轉換整理總結，mysql整理總結

編輯：MySQL綜合教程

MySQL的隱式類型轉換整理總結，mysql整理總結


前言

前幾天在看到一篇文章：價值百萬的 MySQL 的隱式類型轉換感覺寫的很不錯，再加上自己之前也對MySQL的隱式轉化這邊並不是很清楚，所以就順勢整理了一下。希望對大家有所幫助。

當我們對不同類型的值進行比較的時候，為了使得這些數值「可比較」（也可以稱為類型的兼容性)，MySQL會做一些隱式轉化（Implicit type conversion）。

比如下面的例子：

mysql> SELECT 1+'1';
 -> 2
mysql> SELECT CONCAT(2,' test');
 -> '2 test'

很明顯，上面的SQL語句的執行過程中就出現了隱式轉化。並且從結果們可以判斷出，第一條SQL中，將字符串的“1”轉換為數字1，而在第二條的SQL中，將數字2轉換為字符串“2”。

MySQL也提供了CAST()函數。我們可以使用它明確的把數值轉換為字符串。當使用CONCA()函數的時候，也可能會出現隱式轉化，因為它希望的參數為字符串形式，但是如果我們傳遞的不是字符串呢：

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
 -> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
 -> 38.8, '38.8'

隱式轉化規則

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

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. 所有其他情況下，兩個參數都會被轉換為浮點數再進行比較

注意點

安全問題：假如 password 類型為字符串，查詢條件為 int 0 則會匹配上。

mysql> select * from test;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
+----+-------+-----------+
2 rows in set (0.00 sec)

mysql> select * from test where name = 'test1' and password = 0;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
+----+-------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message   |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'password1' |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

相信上面的例子，一些機靈的同學可以發現其實上面的例子也可以做sql注入。

假設網站的登錄那塊做的比較挫，使用下面的方式：

SELECT * FROM users WHERE username = '$_POST["username"]' AND password = '$_POST["password"]'

如果username輸入的是a' OR 1='1，那麼password隨便輸入，這樣就生成了下面的查詢：

SELECT * FROM users WHERE username = 'a' OR 1='1' AND password = 'anyvalue'

就有可能登錄系統。其實如果攻擊者看過了這篇文章，那麼就可以利用隱式轉化來進行登錄了。如下：

mysql> select * from test;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
| 3 | aaa | aaaa |
| 4 | 55aaa | 55aaaa |
+----+-------+-----------+
4 rows in set (0.00 sec)

mysql> select * from test where name = 'a' + '55';
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 4 | 55aaa | 55aaaa |
+----+-------+----------+
1 row in set, 5 warnings (0.00 sec)

之所以出現上述的原因是因為：

mysql> select '55aaa' = 55;
+--------------+
| '55aaa' = 55 |
+--------------+
| 1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a' + '55';
+------------+
| 'a' + '55' |
+------------+
| 55 |
+------------+
1 row in set, 1 warning (0.00 sec)

下面通過一些例子來復習一下上面的轉換規則：

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)

mysql> select 'aa' + 1;
+----------+
| 'aa' + 1 |
+----------+
| 1 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message  |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

把字符串“aa”和1進行求和，得到1，因為“aa”和數字1的類型不同，MySQL官方文檔告訴我們：

     When an operator is used with operands of different types, type conversion occurs to make the operands compatible.

查看warnings可以看到隱式轉化把字符串轉為了double類型。但是因為字符串是非數字型的，所以就會被轉換為0，因此最終計算的是0+1=1

上面的例子是類型不同，所以出現了隱式轉化，那麼如果我們使用相同類型的值進行運算呢？

mysql> select 'a' + 'b';
+-----------+
| 'a' + 'b' |
+-----------+
|  0 |
+-----------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message    |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

是不是有點郁悶呢？

之所以出現這種情況，是因為+為算術操作符arithmetic operator 這樣就可以解釋為什麼a和b都轉換為double了。因為轉換之後其實就是：0+0=0了。

再看一個例子：

mysql> select 'a'+'b'='c';
+-------------+
| 'a'+'b'='c' |
+-------------+
|  1 |
+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message    |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'c' |
+---------+------+---------------------------------------+
3 rows in set (0.00 sec)

現在就看也很好的理解上面的例子了吧。a+b=c結果為1，1在MySQL中可以理解為TRUE，因為'a'+'b'的結果為0，c也會隱式轉化為0，因此比較其實是：0=0也就是true，也就是1.

第二個需要注意點就是防止多查詢或者刪除數據

mysql> select * from test;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | test1 | password1 |
| 2 | test2 | password2 |
| 3 | aaa | aaaa |
| 4 | 55aaa | 55aaaa |
| 5 | 1212 | aaa |
| 6 | 1212a | aaa |
+----+-------+-----------+
6 rows in set (0.00 sec)

mysql> select * from test where name = 1212;
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 5 | 1212 | aaa |
| 6 | 1212a | aaa |
+----+-------+----------+
2 rows in set, 5 warnings (0.00 sec)

mysql> select * from test where name = '1212';
+----+------+----------+
| id | name | password |
+----+------+----------+
| 5 | 1212 | aaa |
+----+------+----------+
1 row in set (0.00 sec)

​上面的例子本意是查詢id為5的那一條記錄，結果把id為6的那一條也查詢出來了。我想說明什麼情況呢？有時候我們的數據庫表中的一些列是varchar類型，但是存儲的值為‘1123'這種的純數字的字符串值，一些同學寫sql的時候又不習慣加引號。這樣當進行select，update或者delete的時候就可能會多操作一些數據。所以應該加引號的地方別忘記了。

關於字符串轉數字的一些說明

mysql> select 'a' = 0;
+---------+
| 'a' = 0 |
+---------+
| 1 |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> select '1a' = 1;
+----------+
| '1a' = 1 |
+----------+
| 1 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> select '1a1b' = 1;
+------------+
| '1a1b' = 1 |
+------------+
|  1 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> select '1a2b3' = 1;
+-------------+
| '1a2b3' = 1 |
+-------------+
|  1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a1b2c3' = 0;
+--------------+
| 'a1b2c3' = 0 |
+--------------+
|  1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

從上面的例子可以看出，當把字符串轉為數字的時候，其實是從左邊開始處理的。

  1. 如果字符串的第一個字符就是非數字的字符，那麼轉換為數字就是0
  2. 如果字符串以數字開頭
  3. 如果字符串中都是數字，那麼轉換為數字就是整個字符串對應的數字
  4. 如果字符串中存在非數字，那麼轉換為的數字就是開頭的那些數字對應的值

總結

以上就是這篇文章的全部內容了，如果你有其他更好的例子，或者被隱式轉化坑過的情況，歡迎分享。希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。

  1. 上一頁:
  2. 下一頁:
MySQL綜合教程
連接MySQL 數據庫的最簡單方案

此文章主要向大家介紹的是最簡單的連接MySQL 數據庫的實
Mysql下載安裝、部署與圖形化詳細操作教程，mysql下載安裝

Mysql下載安裝、部署與圖形化詳細操作教程，mysql下載
MySQL的重裝問題解決

  最近在工作上遇到了MySQL重裝的問題，
MySQL的查詢緩存機制基本學習教程，

MySQL的查詢緩存機制基本學習教程，MySQL緩存機制簡單
詳解mysql語法left join,right join,inner join

詳解mysql語法left join,right jo
MySQL啟動錯誤解決方法，mysql錯誤解決方法

MySQL啟動錯誤解決方法，mysql錯誤解決方法一般情況下
相關文章
閱讀排行榜
MySQL外鏈包含的實際應用命令與具體運用 sql語句中刪除表數據drop、truncate和delete的用法 mysql性能問題 mysql批量刪除相同前綴的表和修改表名 解析MYSQL授權原則 MySQL數據庫，性能監控 mysql 數據連接教程 linux下安裝升級mysql到新版本(5.1-5.7)，mysql5.1-5.7 免安裝mysql配置，免安裝mysql 解決mysql輸入密碼閃退的問題，mysql輸入密碼閃退 MySQL數據庫中primary key重復處理3方案
熱門圖文
Oracle查看表空間的使用情況描述 基於MySql的擴展功能生成全局ID 用Oracle和SQL Server數據庫組合利弊分析 把sql server所有表的所有者改為dbo SQL Server編寫函數獲取漢字的拼音碼 SQL2005Express中導入ACCESS數據庫的兩種方法，access數據庫導入 MySQL本地可以連接，遠程連接不上的問題 redisclientprotocol實現
欄目導航
MYSQL入門知識SQL基礎知識關於MYSQL數據庫Mariadb數據庫MySQL綜合教程
Copyright © 程式師世界 All Rights Reserved