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

The main content of this article is to explain how to solve the case-insensitive problem of MySQL query. Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "the solution to MySQL query case-insensitive method"!

Today, we encountered a situation that when querying data, the query condition input, regardless of case, can find the same data. For example: enter aaa or aaA, AAA can query the same results, indicating that the query conditions are case-insensitive.

Because the program uses the hibernate framework to access mysql. At first I thought it was hibernate. The most tracked hibernate code does not see the step of case conversion, and finally confirms that hibernate is really nothing more than converting hql to sql. Case insensitivity has nothing to do with hibernate.

So he suspected the problem of Mysql. Do an experiment: directly use the client to query the database with sql. It is found that it is indeed insensitive to size.

By querying the data, it is found that you need to set up collate (proofreading). Collate rules:

* _ bin: indicates binary case sensitive collation, that is, it is case-sensitive

* _ cs: case sensitive collation, case sensitive

* _ ci: case insensitive collation, case-insensitive

Www.2cto.com

The solution.

1. You can enclose the query criteria in binary (). For example: select * from TableA where columnA like binary ('aaa')

two。 You can change the collation of this field to binary

For example:

ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR (50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

Problem solved.

At this point, I believe you have a deeper understanding of the "case-insensitive solution to MySQL queries". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.