In mysql, if we want to perform Fuzzy Data Query, we can use like with the % number to perform the query. Next I will briefly introduce how to use mysql like.
Generally, mysql in queries can be written like this.
The Code is as follows: |
Copy code |
SELECT * FROM 'tb _ require' WHERE 'require _ id' IN (23,102 4) |
This method is generally applicable to the numeric type. If it is a string, single quotation marks must be added. For example:
The Code is as follows: |
Copy code |
SELECT * FROM 'tb _ require' WHERE 'require _ name' IN ('aaa', 'bbbbbb ') |
If you want to fuzzy match a string, you can use like to add %. For example:
The Code is as follows: |
Copy code |
SELECT * FROM 'tb _ require' WHERE 'require _ name' LIKE '% aaa %' |
What if we need to fuzzy query multiple strings? How do I write if I like to add in?
In this case, you can use the mysql CONCAT function.
The Code is as follows: |
Copy code |
SELECT * FROM MERs WHERE 'Robert Bob Smith III, PhD. 'like concat (' % ', name,' % ')
|
This solves the like in problem.
Note that CONCAT must be followed by parentheses without spaces. If there is space, an error may be reported.
Note that when you use SQL mode, you cannot use = or! =; And use the LIKE or not like comparison operator.
SELECT field FROM table WHERE a field Like Condition
SQL provides four matching modes for conditions:
1, %: represents any or multiple characters. It can match characters of any type and length.
For example
The Code is as follows: |
Copy code |
SELECT * FROM [user] WHERE u_name LIKE '% 3%' |
We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang sanzang.
In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.
The Code is as follows: |
Copy code |
SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %' |
If you use
The Code is as follows: |
Copy code |
SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %' |
Although three-legged cats can be searched, three-legged cats cannot be searched ".
2, _: represents any single character. Matches any character. It is often used to limit the character length of an expression: (can represent a Chinese character)
For example
The Code is as follows: |
Copy code |
SELECT * FROM [user] WHERE u_name LIKE '_ 3 _' |
Only find that the u_name such as "Tang sanzang" is three characters and the middle word is "three;
For example
The Code is as follows: |
Copy code |
SELECT * FROM [user] WHERE u_name LIKE 'three __'; |
Find out that the name of the "three-legged cat" is three characters and the first word is "three;