Shulou(Shulou.com)06/01 Report--

This article mainly tells you how to achieve implicit conversion in mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to achieve implicit conversion in mysql can bring you some practical help.

We look at the figure above, yhtest table, the third is listed as c, varchar type, four rows of data in the table, when we use select * from yhtest where cantilever; to query, there are three warning!

Today we will start with these three warning, and most of the following can explain why there are these three warnings!

Conversion principle: when we use where conditional query, when the field type and assignment type are inconsistent, they will be converted to integers! Such a description is actually rather vague, let's look at it in detail below. In the figure above, the field type is varchar string, but the query condition is cym0 × × ×. You need to convert all the field contents to integers. The conversion method is as follows:

1) if the string content is pure, the direct conversion to 0 'abc' will be directly converted to 0.

2) if it contains a string that begins with a number, it will be intercepted until it is not a number, for example, '4abc' will be converted to 4, and' 04abc' will be converted to '04', but this will be matched when it matches canti4.

3) if it is a pure numeric string, it will be directly converted to the corresponding × ×, for example,'4' will be directly converted to 4.

4) those that begin with a character and end with a number will still be converted to 0

In this way, when we look at the warnings reported at the beginning, we immediately feel reasonable.

Many articles on sql optimization emphasize that strings must remember to put quotation marks in the where condition, and that's why. Let's take a look at the example.

The results and execution plan for the implementation of select from yhtest where Crun4 are as follows:

The results and execution plan of the implementation of select from yhtest where clock4' are as follows:

Through the execution plan, we can see that the field type and assignment type behind the first where condition are inconsistent, implicit conversion, and no use of the index on column c. In the second method, there is no implicit conversion, the index of c column is used, and the query results are not the same!

Most of the time, we need to use the implicit conversion to avoid the sql performance problems caused by the implicit conversion, but when there is little data, we can do some special operations, such as' abcdefg'=0?

Mysql how to achieve implicit conversion will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.