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

This article mainly explains "the usage of MySQL fuzzy query syntax". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the usage of MySQL fuzzy query grammar.

MySQL provides standard SQL pattern matching and an extended regular expression based on utilities like Unix such as vi, grep and sed

The format of pattern matching.

SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters).

In MySQL, the mode of SQL ignores case by default. Some examples are shown below. Note that when you use SQL mode, you do not

You can use the = or! =; and use the LIKE or NOT LIKE comparison operator.

To find out the name that starts with "b":

> SELECT * FROM pet WHERE name LIKE "b%"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Buffy | Harold | dog | f | 1989-05-13 | NULL |

| | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | |

+-+ +

To find out the name that ends with "fy":

Mysql > SELECT * FROM pet WHERE name LIKE "fy"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| | Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-+ +

To find a name that contains a "w":

Mysql > SELECT * FROM pet WHERE name LIKE "w"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Claws | Gwen | cat | m | 1994-03-17 | NULL |

| | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | |

| | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

+-+ +

To find a name that contains exactly 5 characters, use the "_" mode character:

Mysql > SELECT * FROM pet WHERE name LIKE "_"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Claws | Gwen | cat | m | 1994-03-17 | NULL |

| | Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-+ +

Other types of pattern matching provided by MySQL are using extended regular expressions. When you do matching tests on such patterns, use the

REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some of the characters that extend regular expressions are:

"." Matches any single character.

A character class "[.]" Matches any character in square brackets. For example, "[abc]" matches "a", "b", or "c".

To name a range of characters, use a "-". "[amurz]" matches any lowercase letter, while "[0-9]" matches any "

What number.

"*" matches zero or more things in front of it. For example, "x*" matches any number of "x" characters, "[0-9] *"

Matches any number of numbers, while ". *" matches any number of anything.

Regular expressions are case-sensitive, but if you want, you can use one character class to match both. For example,

"[aA]" matches lowercase or uppercase "a" and "[a-zA-Z]" matches any of the two letters.

If it appears anywhere in the value being tested, the pattern matches (as long as they match the entire value, the SQL pattern matches).

To locate a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or in the

It ends with "$".

To illustrate how extended regular expressions work, the LIKE query shown above is rewritten using REGEXP below:

To find names that start with "b", use "^" to match the beginning of the name and "[bB]" to match lowercase or uppercase "b":

Mysql > SELECT * FROM pet WHERE name REGEXP "^ [bB]"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Buffy | Harold | dog | f | 1989-05-13 | NULL |

| | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | |

+-+ +

To find the name that ends with "fy", use "$" to match the end of the name:

Mysql > SELECT * FROM pet WHERE name REGEXP "fy$"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| | Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-+ +

To find a name that contains a "w", use "[wW]" to match a lowercase or uppercase "w":

Mysql > SELECT * FROM pet WHERE name REGEXP "[wW]"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Claws | Gwen | cat | m | 1994-03-17 | NULL |

| | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | |

| | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

+-+ +

Since a regular expression appears anywhere in the value and its pattern matches, it is no longer necessary to include two of the patterns in the previous query

The aspect places a wildcard to make it match the entire value, just like if you use a SQL pattern.

To find a name that contains exactly five characters, use "^" and "$" to match the beginning and end of the name, and 5 "." The example is in

Between the two:

Mysql > SELECT * FROM pet WHERE name REGEXP "^. $"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Claws | Gwen | cat | m | 1994-03-17 | NULL |

| | Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-+ +

You can also rewrite the previous query using the "{n}"repeat n times" operator:

Mysql > SELECT * FROM pet WHERE name REGEXP "^. {5} $"

+-+ +

| | name | owner | species | sex | birth | death | |

+-+ +

| | Claws | Gwen | cat | m | 1994-03-17 | NULL |

| | Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-+ +

Find numbers and other fuzzy query statements

Select * from pet where name REGEXP "[^ a-zA-Z]."

At this point, I believe you have a deeper understanding of "the usage of MySQL fuzzy query syntax". 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.