前言

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

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

數(shù)據(jù)結(jié)構(gòu)

本文中所有的操作，都是基于該數(shù)據(jù)結(jié)構(gòu)(有興趣的童鞋，可以實(shí)驗(yàn))：

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)建時(shí)間"，  
updated_at datetime null default now() comment "修改時(shí)間"  )

### 新建索引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ù)結(jié)構(gòu)中，我們來看看下面這個(gè)執(zhí)行計(jì)劃：

explain select * from t_base_user where telephone=12345678901;

執(zhí)行計(jì)劃結(jié)果：

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

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

什么是隱式類型轉(zhuǎn)換？

在MySQL中：

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

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

哦... 原來在數(shù)據(jù)結(jié)構(gòu)中telephone字段為字符串(varchar)類型，而我們傳的手機(jī)號(hào)是數(shù)字類型。

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

select * from t_base_user where telephone='12345678901';

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

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

結(jié)果：

從這里看，現(xiàn)在語句已經(jīng)走索引了。為了加深我們對(duì)隱式類型轉(zhuǎn)換的印象，我們?cè)俣嗫纯磶讉€(gè)隱式類型轉(zhuǎn)換案例:

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

mysql > SELECT 1+'1';

結(jié)果：

mysql > 2

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

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

結(jié)果：

'1024,' andyqian';

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

如何避免隱式類型轉(zhuǎn)換?

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

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

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

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

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

我們可以使用CAST顯示的將類型進(jìn)行轉(zhuǎn)換，如下所示:

mysql> SELECT 38.8, CAST(38.8 AS CHAR);

結(jié)果：

mysql > 38.8, '38.8'

如上述中：

select * from t_base_user where telephone=cast(12345678901 as char);

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

你看，這個(gè)時(shí)候也走索引了。

3. 類型一致

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

小心隱式類型轉(zhuǎn)換

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

小結(jié)：

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

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

今天的命令是：

show full columns from table_name;

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

例如：

show full columns from t_base_user;

返回結(jié)果如下圖所示:

其中：

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

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

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論