MySQL fuzzy matching query like,regexp,in Summarythe content is relatively simple, no summary. Key Wordsfuzzy query like regexp in contact Bodyis the data table information used by the sampleMySQL provides standard SQL pattern matching and an extended regular expression pattern matching format based on Unix utilities like VI, grep, and sedFirst, SQL modeThe pattern matching of SQL allows you to match any single character with "_", while "%" matches any number of characters (including 0 characters). In MySQL, the default mode of SQL is case-insensitive. Note that when you use SQL mode, you cannot use = or! Instead, use a like or not-like comparison operator. syntax: SELECT field from table WHERE a field like conditionin terms of conditions, SQL provides two matching patterns:1. Percent (%): shows any or more characters that can match any type and length of character. Example 1:select * from character WHERE name like '% grandchild '; that is, match e named "Sun Xing", "Walker," "Walker" three rows of dataExample 2:select * from character WHERE name like '% Sun% ' and name like '% line% '; match name "Sun Xing", "Walker," "Walker" three rows of dataExample 3:select * from character WHERE name like '% grandchild% row% '; only one row of data named "Sun Xing" can be matchedNote the difference between example 2 and Example 32. Underscore (_): represents any single character. Matches a single arbitrary character, which is commonly used to restrict the expression's character-length statement: (which can represent one Chinese character)Example 1:select * from character WHERE name is like ' _ three _ '; that is, a row of data with the name "Don Sanzang"Example 2:select * from character WHERE name is like ' _ three '; that is, the name is "three" rows of dataSecond, the regular modeother types of pattern matching provided by MySQL are the use of extended regular expressions. When you perform a match test on such a pattern, use the regexp and not regexp operators (or rlike and not rlike, which are synonyms), and the following are the characters commonly used in regular expressions1. "." Matches any single character (single-byte character). A character class "[... ] "matches any character within the square brackets. Example 1:: "[ABC]" matches "a", "B" or "C". To name a range of characters, use a "-". "[A-z]" matches any lowercase letter, while "[0-9]" matches any number. 2. "*" matches 0 or more things in front of it. Example: "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of anything. 3. Note: Regular expressions are case-sensitive, but if you want, you can use a character class to match two types of writing. For example, "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches any letter of two notation. 4. "^" matches the beginning of the nameExample: SELECT * from role WHERE name REGEXP "^ grandchild" with the name "Sun"; that is, match name "Sun Xing", "Monkey King two rows of data5. Use "$" to match the end of the nameExample: Example: SELECT * from role WHERE name REGEXP "grandchild $" whose name ends with "grandchild"; that is, match name "grandchild", "Walker" two rows of datathree, in querycall in query is not very reasonable, the more reasonable argument is: in actually and = similar, the difference is: = followed by a value. In the following can be multiple values. Example 1:select * from role where name in ("Tang three"); that is, match the name "Tang three" row of data;Example 2:select * from role where name in ("Tang Three", "Tang Sanzang"); that is, match the name "Tang Three", "Tang Sanzang" row of data;Example 3:select * from role where name in ("Three", "% runner%"), matching only the name "three" row of data; that in query does not support fuzzy queries, such as Example 4Example 4:select * from role where name in ("% Walker%"), no matching resultFour, like the contact fuzzy querythe CONCAT (str1,str2,...) function returns the string that results from the connection parameter. Example 1:select * from role where name is like contact ("%", "three", "%"); that is, match the name "Tang Three", "Tang Sanzang" two rows of data;The powerful place of the like contact Fuzzy query is that a query can be made on the parameters passed in, such as the data on the front-end submission, assigned to the parameter name, then the SELECT * from table where the name like contact ("%", ${ Name}, "%")
MySQL Fuzzy matching query like,regexp,in
