About MySQL implicit conversions

If the table defines a varchar field and the number is passed in, an implicit conversion occurs.

1. Table 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, the SQL to pass int

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. String-Passed 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 "/>

Look closely at the table structure, the field type of 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"/>

When the user comes in int, there is an implicit conversion problem, and implicit conversions cause a full table scan.

Change the input to a string type and execute the plan as follows, and it will be quick.

In addition, it is important to note that:

0001 of the numeric type is equivalent to 1

0001 and 1 of strings are not equivalent

Second, if the table defines an int field, the string is passed in, no implicit conversions occur within the range of int, and if the range is out of scope and the size of the comparison (' $ ' compared to the string type) is implicitly converted.

1. Table 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 that does not exceed the range string

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, not over-range data-borne 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 out-of-range string

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, out-of-range data-passing 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 "/>

This article is from the "10937712" blog, please be sure to keep this source http://10947712.blog.51cto.com/10937712/1845609

About MySQL implicit conversions