前言

今天我們繼續(xù)回到MySQL系列文章中，談一談MySQL中隱式類型轉換。

(其實我最早知道是在慢SQL優(yōu)化中知道隱式類型轉換概念的)，在說隱式類型轉換之前，首先我們通過一個實例來看看是怎么回事。

數(shù)據(jù)結構

本文中所有的操作，都是基于該數(shù)據(jù)結構(有興趣的童鞋，可以實驗)：

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 "創(chuàng)建時間"，  
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());

引子

首先我們基于上述數(shù)據(jù)結構中，我們來看看下面這個執(zhí)行計劃：

explain select * from t_base_user where telephone=12345678901;

執(zhí)行計劃結果：

細心的童鞋應該已經看出來了，為什么數(shù)據(jù)結構中已經在telephone字段上新建了idx_telephone索引，而上述語句并沒有走索引，而是全表掃描。

這是為什么呢？帶著這疑問，我們來看看今天的主角------MySQL隱式類型轉換

什么是隱式類型轉換？

在MySQL中：

當操作符與不同類型的操作數(shù)一起使用時，會發(fā)生類型轉換以使操作數(shù)兼容。則會發(fā)生轉換隱式

也就是說，MySQL會根據(jù)需要自動將數(shù)字轉換為字符串，將字符串轉換數(shù)字?？吹竭@個概念之后，是不是有一種茅塞頓開的感覺。

哦... 原來在數(shù)據(jù)結構中telephone字段為字符串(varchar)類型，而我們傳的手機號是數(shù)字類型。

現(xiàn)在我們將SQL修改下：

select * from t_base_user where telephone='12345678901';

再看看上述語句的執(zhí)行計劃：

explain select * from t_base_user where telephone='12345678901';

結果：

從這里看，現(xiàn)在語句已經走索引了。為了加深我們對隱式類型轉換的印象，我們再多看看幾個隱式類型轉換案例:

案例一：字符串轉換為數(shù)字

mysql > SELECT 1+'1';

結果：

mysql > 2

案例二：數(shù)字轉換為字符串

mysql -> SELECT CONCAT(1024,' andyqian');

結果：

'1024,' andyqian';

此時CONCAT(字符拼接)函數(shù)就將1024進行了隱式類型轉換。

如何避免隱式類型轉換?

只有當清楚的知道隱式類型轉換的規(guī)則，才能從根本上避免產生隱式類型轉換。

MySQL也在官網(wǎng)描述了進行隱式類型轉換的一些規(guī)則如下：

1. 隱式類型轉換規(guī)則

• 如果一個或兩個參數(shù)都是NULL，比較的結果是NULL，除了NULL安全的<=>相等比較運算符。對于NULL <=> NULL，結果為true。不需要轉換
• 如果比較操作中的兩個參數(shù)都是字符串，則將它們作為字符串進行比較。
• 如果兩個參數(shù)都是整數(shù)，則將它們作為整數(shù)進行比較。
• 如果不與數(shù)字進行比較，則將十六進制值視為二進制字符串
• 如果其中一個參數(shù)是十進制值，則比較取決于另一個參數(shù)。 如果另一個參數(shù)是十進制或整數(shù)值，則將參數(shù)與十進制值進行比較，如果另一個參數(shù)是浮點值，則將參數(shù)與浮點值進行比較
• 如果其中一個參數(shù)是TIMESTAMP或DATETIME列，另一個參數(shù)是常量，則在執(zhí)行比較之前將常量轉換為時間戳。
• 在所有其他情況下，參數(shù)都是作為浮點數(shù)（實數(shù)）比較的。

2. 使用CAST函數(shù)顯示轉換

我們可以使用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);

查看執(zhí)行計劃，我們也可以看出

你看，這個時候也走索引了。

3. 類型一致

這里說的類型一致，指的是在寫SQL時，參數(shù)類型一定要與數(shù)據(jù)庫中的類型一致，避免產生隱式類型轉換，就如剛才在文首時，如果多檢查，寫的SQL的參數(shù)類型與數(shù)據(jù)庫中字段類型一致，也就不會不走索引了，你說是不是？

小心隱式類型轉換

這里再重申一次，寫SQL時一定要檢查參數(shù)類型與數(shù)據(jù)庫字段類型一致，(如果參數(shù)不一致，也要使用CAST函數(shù)顯示轉換成一致)否則造成隱式類型轉換，不走索引，后果簡直不堪設想， 在前面《寫會MySQL索引》這篇文章中提到過，不走索引，輕則造成慢查詢，重則造成數(shù)據(jù)庫服務器CPU100%。唉，說到這里，不瞞你說，我就吃過不少MySQL隱式類型轉換的虧 !(如慢查詢) !

小結：

看到這里，是不是有一種，數(shù)據(jù)表設計還真不是件容易的事情。需要考慮的因素太多太多了，需要考慮字段類型，索引設計，還有各種約束條件等等。也一定要謹慎謹慎再謹慎！其實換個角度就更容易理解了，大家都知道高樓大廈都是需要一個好的地基的，在數(shù)據(jù)庫表設計中，前期的表結構設計就是這個地基，其重要性可想而知。

從后續(xù)開始，每篇MySQL文章最后，都推薦一個常用且實用的MySQL命令:

今天的命令是：

show full columns from table_name;

作用: 顯示指定表所有列信息

例如：

show full columns from t_base_user;

返回結果如下圖所示:

其中：

• Field: 字段名
• Type: 該字段類型
• Collation: 描述了如何對查詢出來的數(shù)據(jù)進行比較和排序
• Null: 是否允許為空， NO: 不允許，YES 允許
• Key: 鍵，例如: 主鍵(PRI)， 唯一鍵(UNI) 等
• Default: 該字段默認值 Extra: 附加信息如自增主鍵上的(auto_increment)
• Privileges: 權限，有select，update等
• Comment: 字段注釋

注意：通過該命令顯示都是建表時的信息，這里著重強調一下，在數(shù)據(jù)庫建表時，在每個字段上， 一定要加注釋，加注釋，加注釋！

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論