Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the comparison of uppercase and lowercase characters of varchar in MySQL". In daily operation, I believe that many people have doubts about the comparison of uppercase and lowercase characters of varchar in MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "comparison of uppercase and lowercase characters of varchar in MySQL". Next, please follow the editor to study!

Background:

According to the SQL problem raised by the program one day, it is found that when comparing the contents of varchar strings, they are not case-sensitive.

The test environment restores the problem. The environment is constructed as follows:

Create table case_sen (id int primary key, name varchar (32) notnull)

Insert into case_sen values (1 recording LucyLoveLily`)

Insert into case_sen values (2 million Lucylovelly')

Insert into case_sen values (3 million TomHateLarry')

Insert into case_sen values (4 million tomhatelarry')

The effect is as follows:

In fact, the program wants only id=1 to match to

The same problem occurs in like:

The program asks whether it is a lower_case_table_names problem, obviously. Not _ (: "∠") _

Scene:

MySQL-5.7 restore, in fact, this problem has nothing to do with the version

Analysis:

First of all, we can confirm that there is no problem with the data in the table, that is, uppercase or uppercase, lowercase or lowercase.

Then the problem is most likely to lie in the MySQL operator "=" and like, the principle of these two types of operations may be due to some reason / setting / resulting in not case-sensitive

After the direction of troubleshooting is determined, try to look in the document with the keywords comparison and case sensitive, and find that there is a chapter that mentions this problem, and gives some examples.

PS: chapter name B.5.4.1 Case Sensitivity in String Searches

An example of the document is shown in the figure:

Summarize the description of the document: when a non-binary string performs a logical operation, the result is calculated according to the configuration of the collate.

So if the setting of collate is not case-sensitive, then the effect will appear in the test environment.

So the question is, what is the default collate for utf8mb4?

It is found that it is utf8mb4_general_ci. Through the analysis of the content of the document, the suffix ci should mean case-insensitive.

Then try changing the collate of the character set to see if it is case-sensitive.

Bingo~~~

Of course, specifying collate as utf8mb4_bin on the column can also achieve the same goal.

PS: all character sets of mysql use XXX_general_ci by default, and some character sets have XXX_general_cs, but utf8 does not, so use utf8_bin

PPS: interest in reading: https://www.percona.com/live/europe-amsterdam-2015/sites/default/files/slides/PL_AMS_Unicode_Booking169_v3.pdf

PS: pay attention to the index. 5.7 is fine.

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.