前言
今天我們繼續回到MySQL系列文章中,談一談MySQL中隱式型別轉換。(其實我最早知道是在慢SQL優化中知道隱式型別轉換概念的),在說隱式型別轉換之前,首先我們通過一個例項來看看是怎麼回事。
資料結構
本文中所有的操作,都是基於該資料結構(有興趣的童鞋,可以實驗):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
create table t_base_user( oid bigint(20) not null primary key auto_increment, name varchar(30) null comment "name", email varchar(30) null comment "email", age int null comment "age", telephone varchar(30) null comment "telephone", status tinyint(4) null comment "0 無效 1 有效", created_at datetime null default now() comment "建立時間", updated_at datetime null default now() comment "修改時間" ) ### 新建索引alter table t_base_user add index idx_email(email); alter table t_base_user add index idx_name(name); alter table t_base_user add index idx_telephone(telephone); ### 新增記錄: INSERT INTO `andyqian`.`t_base_user` (`name`, `email`, `age`, `telephone`, `status`, `created_at`, `updated_at`) VALUES ('111111', 'andytohome@gmail.com', '111', '12345678901', '1', now(),now()); |
引子
首先我們基於上述資料結構中,我們來看看下面這個執行計劃:
explain select * from t_base_user where telephone=12345678901;
執行計劃結果:
細心的童鞋應該已經看出來了,為什麼資料結構中已經在telephone欄位上新建了idx_telephone索引，而上述語句並沒有走索引,而是全表掃描。這是為什麼呢？帶著這疑問,我們來看看今天的主角——MySQL隱式型別轉換
什麼是隱式型別轉換？
在MySQL中:
當操作符與不同型別的運算元一起使用時，會發生型別轉換以使運算元相容。則會發生轉換隱式
也就是說,MySQL會根據需要自動將數字轉換為字串，將字串轉換數字。看到這個概念之後,是不是有一種茅塞頓開的感覺。哦… 原來在資料結構中telephone欄位為字串(varchar)型別,而我們傳的手機號是數字型別。現在我們將SQL修改下:
select * from t_base_user where telephone=’12345678901′;
再看看上述語句的執行計劃:
explain select * from t_base_user where telephone=’12345678901′;
結果:
從這裡看,現在語句已經走索引了。為了加深我們對隱式型別轉換的印象,我們再多看看幾個隱式型別轉換案例:
案例一: 字串轉換為數字
mysql > SELECT 1+’1′;
結果:
mysql > 2
案例二: 數字轉換為字串
mysql -> SELECT CONCAT(1024,’ andyqian’);
結果:
‘1024,’ andyqian’;
此時CONCAT(字元拼接)函式就將1024進行了隱式型別轉換。
如何避免隱式型別轉換?
只有當清楚的知道隱式型別轉換的規則，才能從根本上避免產生隱式型別轉換。MySQL也在官網描述了進行隱式型別轉換的一些規則如下:
1. 隱式型別轉換規則:
- 如果一個或兩個引數都是NULL，比較的結果是NULL，除了NULL安全的<=>相等比較運算子。對於NULL <=> NULL，結果為true。不需要轉換
- 如果比較操作中的兩個引數都是字串，則將它們作為字串進行比較。
- 如果兩個引數都是整數，則將它們作為整數進行比較。
- 如果不與數字進行比較，則將十六進位制值視為二進位制字串
- 如果其中一個引數是十進位制值，則比較取決於另一個引數。 如果另一個引數是十進位制或整數值，則將引數與十進位制值進行比較，如果另一個引數是浮點值，則將引數與浮點值進行比較
- 如果其中一個引數是TIMESTAMP或DATETIME列，另一個引數是常量，則在執行比較之前將常量轉換為時間戳。
- 在所有其他情況下，引數都是作為浮點數（實數）比較的。
2. 使用CAST函式顯示轉換
我們可以使用CAST顯示的將型別進行轉換,如下所示:
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
結果:
mysql > 38.8, ‘38.8’
如上述中:
select * from t_base_user where telephone=cast(12345678901 as char);
檢視執行計劃,我們也可以看出
你看,這個時候也走索引了。
3. 型別一致
這裡說的型別一致,指的是在寫SQL時,引數型別一定要與資料庫中的型別一致,避免產生隱式型別轉換,就如剛才在文首時,如果多檢查,寫的SQL的引數型別與資料庫中欄位型別一致，也就不會不走索引了，你說是不是？
小心隱式型別轉換
這裡再重申一次,寫SQL時一定要檢查引數型別與資料庫欄位型別一致,(如果引數不一致,也要使用CAST函式顯示轉換成一致)否則造成隱式型別轉換,不走索引,後果簡直不堪設想, 在前面《寫會MySQL索引》這篇文章中提到過,不走索引,輕則造成慢查詢，重則造成資料庫伺服器CPU100%。唉,說到這裡,不瞞你說，我就吃過不少MySQL隱式型別轉換的虧 ! (如慢查詢) !
小結
看到這裡,是不是有一種,資料表設計還真不是件容易的事情。需要考慮的因素太多太多了,需要考慮欄位型別,索引設計,還有各種約束條件等等。也一定要謹慎謹慎再謹慎！其實換個角度就更容易理解了,大家都知道高樓大廈都是需要一個好的地基的,在資料庫表設計中,前期的表結構設計就是這個地基，其重要性可想而知。
從後續開始,每篇MySQL文章最後,都推薦一個常用且實用的MySQL命令:
今天的命令是:
show full columns from table_name;
作用: 顯示指定表所有列資訊
例如:
show full columns from t_base_user;
返回結果如下圖所示:
其中:
Field: 欄位名
Type: 該欄位型別
Collation: 描述瞭如何對查詢出來的資料進行比較和排序
Null: 是否允許為空, NO: 不允許，YES 允許
Key: 鍵,例如: 主鍵(PRI), 唯一鍵(UNI) 等
Default: 該欄位預設值 Extra: 附加資訊如自增主鍵上的(auto_increment)
Privileges: 許可權,有select,update等
Comment: 欄位註釋
注意: 通過該命令顯示都是建表時的資訊,這裡著重強調一下,在資料庫建表時,在每個欄位上, 一定要加註釋,加註釋，加註釋！
最後: 大家今天剁手了嗎？ 祝大家晚安!
相關閱讀: