Can’t say no

Working overtime today, the business sister came to us to check the data, said that the data was found to be wrong. SQL > alter table SQL > alter table SQL > alter table SQL

select distinct * from prvt_pub_stmt_vn
where issue_time > = '2020-08-01'
and issue_time < = '2020-08-01'
and prs_dmtd_cde in ('p'.'n');
Copy the code

I analyze to analyze, feel no problem ah, so check the prs_dmtD_CDE field code value, found that there are not only capital P and lowercase P, and sister only check lowercase P, the amount of data is much more.

So I changed the girl’s SQL:

select distinct * from prvt_pub_stmt_vn
where issue_time > = '2020-08-01'
and issue_time < = '2020-08-01'
and prs_dmtd_cde in ('p'.'n'.'P'.'N');
Copy the code

Turns out the results are the same. This is…

In front of the sister of course can not say no ah, so let the sister go back to see.

I quickly checked the Internet and found that it was the encoding format and sorting rules of MySQL.

Know why

Our MySQL database basically uses utF8 encoding, and UTF8 encoding has various collation rules. Common ones are as follows:

Utf8_bin: Stores each character in a string in hexadecimal format and is case sensitive.

Utf8_general_ci: Case insensitive. Ci is short for Case insensitive insensitive insensitive insensitive.

Check the default character set Settings again:

It just so happens that the default collation for the UTF8 encoding is utF8_general_ci — that is, case insensitive.

The solution

If the cause of the problem is found, then the remedy will be right.

The solution, of course, is to change the collate property 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;
Copy the code

Another solution is to change the SQL instead of the existing table structure. Prefix the query field with the binary keyword.

select distinct * from prvt_pub_stmt_vn
where issue_time > = '2020-08-01'
and issue_time < = '2020-08-01'
and binary prs_dmtd_cde in ('p'.'n');
Copy the code

Mysql queries are case-insensitive by default. You can add binary to your SQL statement.

Binary is not a function. It is a conversion operator used to force the string following it to be a binary string, which can be interpreted as case-sensitive when comparing strings.

The last

The problem was solved, of course, to tell the girl how profound the problem was, and how I analyzed the principle of the final solution.

Look at the sister cast adoring eyes, of course, is very happy.

The most important thing is to remember this problem. In the future, when we encounter case-sensitive services, we should pay attention to the choice of character set and sorting rules when building tables, so as to avoid this kind of thing happening today.