Re-understanding of MySQL's where query

##Related learning recommendations:

mysql tutorial

cannotsayno

I was working overtime today, and the sales girl came to us to check the data and said that the data was wrong. I saw that the girl's SQL was written like this:

select distinct * from prvt_pub_stmt_vnwhere issue_time >= &#39;2020-08-01&#39;and issue_time <= &#39;2020-08-01&#39;and prs_dmtd_cde in (&#39;p&#39;,&#39;n&#39;);复制代码

I analyzed it and found that there was no problem, so I checked the code value of the prs_dmtd_cde field and found that there were not only uppercase P but also lowercase p. But the girl only checked the lowercase p, but the amount of data was much larger.

So I changed the girl’s SQL:

select distinct * from prvt_pub_stmt_vnwhere issue_time >= &#39;2020-08-01&#39;and issue_time <= &#39;2020-08-01&#39;and prs_dmtd_cde in (&#39;p&#39;,&#39;n&#39;,&#39;P&#39;,&#39;N&#39;);复制代码

The result turned out to be the same. That's it. . .

Of course you can't say no in front of a girl, so I asked the girl to go back and take a look.

I quickly checked online and found that it turned out to be a problem with MySQL's encoding format and sorting rules.

Know why

Our MySQL database basically uses the utf8 encoding format, and there are various sorting rules for the utf8 encoding format. Commonly used ones are as follows:

utf8_bin: Store each character in the string as hexadecimal data, case-sensitive.

utf8_general_ci: Case insensitive, ci is the abbreviation of case insensitive, that is, case insensitive.

Check the default character set settings again:

It happens that the default sorting rule of utf8 encoding format is: utf8_general_ci - that is, it is not case sensitive.

Solution

If the cause of the problem is found, then we can prescribe the right medicine.

The solution is naturally to directly modify the collate attribute of the field to utf8_bin.

ALTER TABLE prvt_pub_stmt_vn CHANGE prs_dmtd_cde prs_dmtd_cde VARCHAR(255) 
CHARACTER SET utf8 COLLATE utf8_bin;复制代码

Another solution is to change the SQL instead of changing the original table structure. Add the binary keyword before the query field.

select distinct * from prvt_pub_stmt_vnwhere issue_time >= &#39;2020-08-01&#39;and issue_time <= &#39;2020-08-01&#39;and binary prs_dmtd_cde in (&#39;p&#39;,&#39;n&#39;);复制代码

Mysql default query is case-insensitive. You can add binary to the SQL statement to make it case-sensitive.

binary is not a function, but a type conversion operator. It is used to force the string following it to be a binary string. It can be understood that string comparison is case-sensitive.

Finally

The problem was solved. Of course, I told the girl how profound the problem was and how I analyzed the principles and finally solved it.

Of course I am very happy to see the adoring look cast by the girl.

The most important thing is to remember this issue. In the future, when encountering business cases where fields are case-sensitive, you must pay attention to the selection of character sets and sorting rules when building tables to avoid this happening today. .

If you want to know more about programming learning, please pay attention to the

php training column!

The above is the detailed content of Re-understanding of MySQL's where query. For more information, please follow other related articles on the PHP Chinese website!