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

MYSQL pulls from regular query to case-sensitive solution in query. In order to solve this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

The concept of query in MYSQL is mostly simple, not complex, leaving complex things to the program to do, and the database is the concept of a container or a solidified concept.

In fact, with the advent of MYSQL8, more and more statements that can not be executed in SQL can be executed in MYSQL, such as CTE, window functions, and so on. Some queries are checked by like, while some queries in LIKE are more complex, and the use of regular expressions in SQL queries can help the expression and implementation of some complex queries. To sum up, MYSQL is going from being a kid on the street to the Spider-Man who refused to attend the meeting with Iron Man. (if you have recently seen the changes in that version of MYSQL 8.017)

How can we make such a query? let's take a look at the following examples

1 if we want to query the number of people whose names first_name begins with G in the employees table

Here we can use Rlike to manipulate regular expressions

Select count (*) from employees where first_name RLIKE'^ G'

There are more than 16,000 people in the first_name starting with G here.

Here is a requirement to find the firstname that begins with G and ends with I in the middle

The following statements should meet your needs

Select * from employees where first_name RLIKE'^ G [amurz] 'and first_name RLIKE'ify'

3 query the first_name of uo at the beginning of G in the second and third character position

Select * from employees where first_name RLIKE'^ G [uo] {2jue 3}'

4. Some problems faced in practice sometimes need to be solved by regular expressions.

For example, in the following picture, we encounter that first_name is written in a strange way, but if we follow the usual query method, we will query them all.

Select * from employees where first_name = 'georgi'

We use the above statement to query, and it is conceivable that all Georgi gEORGI will be checked out without case sensitivity.

In fact, regular expressions were case-sensitive before the earlier version of MYSQL 5.x.

But the current version is indistinguishable.

How to make current regular expressions case-sensitive

We can add binary to the name of the field you want to query and match the normal regularity, and you can find the data you are looking for. Please see the figure above.

So here comes the question, the title says that it is case-sensitive, can the above one be regarded as case-sensitive? if I don't use regular expressions, or I don't want to add binary in front of the field, how can you solve my case-sensitive problem?

Let's just talk about it, jump directly to the curative effect, and see if I didn't use binary below, I still mentioned that we didn't find the data.

You don't have to be regular.

Some people may immediately say, "there is only one record in your table. Let's take a look at the picture below, of course not."

The question comes up, what did I do to make MYSQL first_name case sensitive?

The answer is in the figure. Just add binary after the modified field.

Okay, let's call it a day.

This is the end of the answer to the question about the case-sensitive solution of MYSQL from regular query to query. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.