MySQLのLike演算子に関する詳細

1. はじめに

Like演算子は、未知またはむしろ既知の部品の値でフィルタリングするときに使用できます。Like演算子はファジーマッチングに使用します。

は2つのワイルドカードをサポートしています。

• % ワイルドカード、複数の文字にマッチする場合に使用
• _ 1文字にマッチするワイルドカード
  

ワイルドカードは、さらにその中の不明な部分によって6種類のマッチに分けられます。

2. 本体

まず、以下に示すDDLとテーブルデータを持つUserテーブルを用意し、これをコピーして直接使用することができます。

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 'primary key',
  `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;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'Li Zixi', 18, 1);
INSERT INTO `user` VALUES (2, 'Zhang San', 22, 1);
INSERT INTO `user` VALUES (3, '李四', 38, 1);
INSERT INTO `user` VALUES (4, '王五', 25, 1);
INSERT INTO `user` VALUES (5, 'Liu Ma Zi', 13, 0);
INSERT INTO `user` VALUES (6, 'Tian Qi', 37, 1);
INSERT INTO `user` VALUES (7, 'Xie Li', 18, 1);

SET FOREIGN_KEY_CHECKS = 1;
Copy the code
The initial order of the data is shown below.

mysql> select * from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Li Zi Hsi | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Six Pocky | 13 | 0 |
| 6 | Tian Qi | 37 | 1 |
| 7 | Xie Li | 18 | 1 |
+ ----+--------+-----+-----+
7 rows in set (0.00 sec)

2.1 % ワイルドカード

ワイルドカードの%にマッチする方法は、次の3つです。 %xx {{コード %xx xx% ,

 %xx%
 and the next shows the simple usage of all three.
Requirements.
Query
user
The user whose last name is Zhang in the table
Statement.
mysql> select * from user where name like 'Zhang%';
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | Zhang San | 22 | 1 |
+----+------+-----+-----+
1 row in set (0.00 sec)




Requirements.
Query
user
Users whose names end with seven in the table
Statement.
mysql> select * from user where name like '%seven';
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 6 | Tianqi | 37 | 1 |
+----+------+-----+-----+
1 row in set (0.00 sec)



Requirements.
Query
user
Users whose names contain the character Li in the table
Statement.
mysql> select * from user where name like '%Li%';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | li zi hii | 18 | 1 |
| 3 | 李四 | 38 | 1 |
+----+--------+-----+-----+
2 rows in set (0.00 sec)




2.2 _Wildcards


_
Wildcards and
%
The difference between wildcards is that _ matches only one character and must match one character, while % can match multiple characters, even 0 characters.
Requirements.
Query
user
Users whose last name is Li and whose first name has only two Chinese characters in the table
statement.
mysql> select * from user where name like 'Li_';
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 3 | Li Si | 38 | 1 |
+----+------+-----+-----+
1 row in set (0.00 sec)





Requirements.
Query
user
The user named three in the table
statement.
mysql> select * from user where name like '_three';
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | Zhang San | 22 | 1 |
+----+------+-----+-----+
1 row in set (0.00 sec)




Requirements.
Query
user
The user whose name is three subs in the table and whose second sub is hemp
statement.
mysql> select * from user where name like '_hemp_';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 5 | six-martial | 13 | 0 |
+----+--------+-----+-----+
1 row in set (0.00 sec)




2.3 Notes on the use of wildcards


Wildcards are very powerful, and I'm sure many of you use them frequently, but string matching is not always a particularly fast performer. So there are some caveats that we should always keep in mind when using wildcards.
The rule of thumb is to avoid all the problems associated with wildcards by not using them, so don't use like if other operators can be queried
Where wildcards are used, try to narrow the scope of the query, and if there are multiple query criteria, you should consider whether you can place the wildcard after the other filter criteria
Pay special attention to the selection of wildcards and the placement of wildcards, you can refer to the six matching methods to choose your appropriate one
At this point this article on
MySQL
of
like
This is the end of the article on operator details, for more information about the MySQL like operator, please search the previous articles of the Codedevlib or continue to browse the following related articles, I hope you will support the Codedevlib more in the future!

mysql> select * from user where name like 'Zhang%';
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | Zhang San | 22 | 1 |
+----+------+-----+-----+
1 row in set (0.00 sec)

user

%

user

like
This is the end of the article on operator details, for more information about the MySQL like operator, please search the previous articles of the BinaryDevelop or continue to browse the following related articles, I hope you will support the BinaryDevelop more in the future!

mysql> select * from user where name like '_hemp_';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 5 | six-martial | 13 | 0 |
+----+--------+-----+-----+
1 row in set (0.00 sec)





2.3 Notes on the use of wildcards



Wildcards are very powerful, and I'm sure many of you use them frequently, but string matching is not always a particularly fast performer. So there are some caveats that we should always keep in mind when using wildcards.



The rule of thumb is to avoid all the problems associated with wildcards by not using them, so don't use like if other operators can be queried


Where wildcards are used, try to narrow the scope of the query, and if there are multiple query criteria, you should consider whether you can place the wildcard after the other filter criteria


Pay special attention to the selection of wildcards and the placement of wildcards, you can refer to the six matching methods to choose your appropriate one


At this point this article on
MySQL
of
like
This is the end of the article on operator details, for more information about the MySQL like operator, please search the previous articles of the BinaryDevelop or continue to browse the following related articles, I hope you will support the BinaryDevelop more in the future!