“This is the 15th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

1, the introduction of

The like operator can be used when filtering values that are unknown or know of a part; The like operator is used for fuzzy matching.

Like supports two wildcards:

  • % wildcard, used to match multiple characters
  • _ Wildcard used to match a single character

Wildcards are divided into six matching methods based on their unknown location:

The matching way role
%xx The xx characters on the right side need to be exactly the same, the left side can be any character,You can also have no characters
_xx The xx characters on the right side need to be exactly the same, and the left side can be any character.Must be a cannot without character
xx% Represents left matching, xx characters on the right side need to be exactly equal, the right side can be any character,You can also have no characters
xx_ The xx characters on the left must be exactly the same, and the right can be any character.Must be a cannot without character
%xx% Represents the middle match, the middle must be exactly equal, the left and right sides can be any character,The left and right sides can have no other characters
xx The middle must be exactly the same. The left and right sides can be any character.The left and right sides must be a single character

2, the body

First prepare a User table, DDL and table data as shown below, can be directly copied use.

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`;  CREATE TABLE 'user' (' id 'bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', 'name' varchar(255) CHARACTER SET utf8 COLLATE UTf8_general_ci NOT NULL COMMENT 'username ', 'age' int(11) NOT NULL COMMENT 'age ',' sex 'smallint(6) NOT NULL COMMENT' gender ', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - the Records of the user -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- INSERT INTO ` user ` VALUES (1, 'plum', 18, 1); INSERT INTO 'user' VALUES (2, '3 ', 22, 1); INSERT INTO 'user' VALUES (3, '55 ', 1); INSERT INTO 'user' VALUES (4, '王五', 25, 1); INSERT INTO 'user' VALUES (5, '6 ', 13, 0); INSERT INTO 'user' VALUES (6, '7 ', 37, 1); INSERT INTO 'user' VALUES (7, '80 ', 1); SET FOREIGN_KEY_CHECKS = 1;Copy the code

The initial order of the data is as follows:

mysql> select * from user; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 2 threes | | 1 | | | 3 |, dick, and harry 38 | 1 | | | 4 fifty and 25 | | 1 | | 5 6 pitted | | | | 0 13 | | 6 notoginseng 37 | 1 | | | | 7 honoraria 18 | 1 | | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 7 rows in the set (0.00 SEC)Copy the code

2.1 % wildcard

The % wildcard can be matched in three ways: %xx, xx%, and %xx%.

Requirements:

Select * from user where last name = zhang

Statement:

Mysql > select * from user where name like '%'; + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | | id name | age | sex | + - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | 2 | zhang SAN 22 | 1 | | +----+------+-----+-----+ 1 row in set (0.00 SEC)Copy the code

Requirements:

Query users whose names end with seven in the user table

Statement:

Mysql > select * from user where name like '% 7 '; + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | | id name | age | sex | + - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | 6 notoginseng 37 | | | 1 +----+------+-----+-----+ 1 row in set (0.00 SEC)Copy the code

Requirements:

Query the users whose names contain plum characters in the user table

Statement:

Mysql > select * from user where name like '% li %'; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 3 |, dick, and harry 38 | 1 | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code

2.2 _ Wildcard

The _ wildcard differs from the % wildcard in that _ matches only one character and must match one character; % can match multiple characters, even zero characters.

Requirements:

Select * from user where username = li and first name = 2

Statement:

Mysql > select * from user where name like ' '; + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | 3 | li si | | 1 | 38 +----+------+-----+-----+ 1 row in set (0.00 SEC)Copy the code

Requirements:

Query user 3 from user

Statement:

Mysql > select * from user where name like '_ 3 '; + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | | id name | age | sex | + - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | 2 | zhang SAN 22 | 1 | | +----+------+-----+-----+ 1 row in set (0.00 SEC)Copy the code

Requirements:

Select * from user where the user name has three children and the second child is ma

Statement:

Mysql > select * from user where name like '_ _'; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 5 6 pitted | | | | | 0 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

2.3 Precautions for Using WildCards

Wildcards are very powerful, and I’m sure many people use them regularly, but string matching is often not a particularly fast thing to do. So there are a few things to keep in mind when using wildcards.

  • If you can’t use it, you can’t avoid all the problems that wildcards cause, so don’t use like if other operators can query it
  • Where wildcards are used, minimize the scope of the query. If there are more than one query criteria, you should consider whether the wildcard can be placed after other filter criteria
  • Pay special attention to the selection of wildcards, and the location of wildcards, you can refer to the six matching methods to choose their own appropriate