關於MySQL隱式轉換

標籤：關於mysql隱式轉換

一、如果表定義的是varchar欄位，傳入的是數字，則會發生隱式轉換。

  1、表DDL

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A9%3A27.png?version=1&modificationDate=1464336560000&api=v2" alt="image2016-5-27%2016%3A9%3A27.png?version" />

2、傳int的sql

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A10%3A11.png?version=1&modificationDate=1464336605000&api=v2" alt="image2016-5-27%2016%3A10%3A11.png?versio" />

3、傳字串的sql

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A10%3A49.png?version=1&modificationDate=1464336642000&api=v2" alt="image2016-5-27%2016%3A10%3A49.png?versio" />

仔細看下錶結構，rid的欄位類型： 650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A9%3A38.png?version=1&modificationDate=1464336572000&api=v2" alt="image2016-5-27%2016%3A9%3A38.png?version" />

而使用者傳入的是int，這裡會有一個隱式轉換的問題，隱式轉換會導致全表掃描。

把輸入改成字串類型，執行計畫如下，這樣就會很快了。

此外，還需要注意的是：

數字類型的0001等價於1

字串的0001和1不等價

二、如果表定義的是int欄位，傳入的是字串，在不超過int範圍內，不會發生隱式轉換，如果超出範圍並且比較大小（以字串類型比較‘$‘）會隱式轉換。

1、表DDL

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A13%3A26.png?version=1&modificationDate=1464336799000&api=v2" alt="image2016-5-27%2016%3A13%3A26.png?versio" />

2、不超範圍傳字串的sql

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A14%3A44.png?version=1&modificationDate=1464336877000&api=v2" alt="image2016-5-27%2016%3A14%3A44.png?versio" />

3、不超範圍傳數位sql

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A15%3A8.png?version=1&modificationDate=1464336902000&api=v2" alt="image2016-5-27%2016%3A15%3A8.png?version" />

 

4、超出範圍傳字串的sql

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A23%3A10.png?version=1&modificationDate=1464337384000&api=v2" alt="image2016-5-27%2016%3A23%3A10.png?versio" />

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A24%3A46.png?version=1&modificationDate=1464337480000&api=v2" alt="image2016-5-27%2016%3A24%3A46.png?versio" />

5、超出範圍傳數位sql

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A23%3A56.png?version=1&modificationDate=1464337430000&api=v2" alt="image2016-5-27%2016%3A23%3A56.png?versio" />

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/13305667/image2016-5-27%2016%3A25%3A6.png?version=1&modificationDate=1464337500000&api=v2" alt="image2016-5-27%2016%3A25%3A6.png?version" />

本文出自 “10937712” 部落格，請務必保留此出處http://10947712.blog.51cto.com/10937712/1845609

關於MySQL隱式轉換