MYSQL必知必会 -- 用通配符进行过滤

LIKE操作符

通配符（wildcard） 用来匹配值的一部分的特殊字符。
搜索模式（search pattern） 由字面值、通配符或两者组合构成的搜索条件。

通配符本身实际是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。

%

%表示任何字符出现任意次数。

MariaDB [test]> insert into linux value('user11','1111','girl',31);
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> insert into linux value('user111','111111','boy',41);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from linux;
+---------+--------+------+------+
| user    | passwd | sex  | age  |
+---------+--------+------+------+
| user1   | 111    | boy  | 18   |
| user2   | 222    | girl | 23   |
| user3   | 333    | boy  | 12   |
| user4   | 444    | boy  | 22   |
| user5   | 555    | boy  | 30   |
| a       | passa  | boy  | 22   |
| 1       | pass1  | girl | 30   |
| user1   | pass1  | girl | 18   |
| user11  | 1111   | girl | 31   |
| user111 | 111111 | boy  | 41   |
+---------+--------+------+------+
10 rows in set (0.00 sec)

MariaDB [test]> select user from linux WHERE user  LIKE 'user%';
+---------+
| user    |
+---------+
| user1   |
| user2   |
| user3   |
| user4   |
| user5   |
| user1   |
| user11  |
| user111 |
+---------+
8 rows in set (0.00 sec)

在模式的两端使用两个通配符：

MariaDB [test]> select * from linux WHERE passwd  LIKE '%ss%';
+-------+--------+------+------+
| user  | passwd | sex  | age  |
+-------+--------+------+------+
| a     | passa  | boy  | 22   |
| 1     | pass1  | girl | 30   |
| user1 | pass1  | girl | 18   |
+-------+--------+------+------+
3 rows in set (0.00 sec)

还可以在中间使用：

MariaDB [test]> select * from linux WHERE user  LIKE 'u%1';
+---------+--------+------+------+
| user    | passwd | sex  | age  |
+---------+--------+------+------+
| user1   | 111    | boy  | 18   |
| user1   | pass1  | girl | 18   |
| user11  | 1111   | girl | 31   |
| user111 | 111111 | boy  | 41   |
+---------+--------+------+------+
4 rows in set (0.00 sec)

重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。

注意：
注意尾空格 尾空格可能会干扰通配符匹配。
注意NULL ，’%’ 不能匹配用值NULL作为产品名的行。

下划线（_）通配符

下划线只匹配单个字符而不是多个字符。

MariaDB [test]> select * from linux WHERE user  LIKE 'user_';
+-------+--------+------+------+
| user  | passwd | sex  | age  |
+-------+--------+------+------+
| user1 | 111    | boy  | 18   |
| user2 | 222    | girl | 23   |
| user3 | 333    | boy  | 12   |
| user4 | 444    | boy  | 22   |
| user5 | 555    | boy  | 30   |
| user1 | pass1  | girl | 18   |
+-------+--------+------+------+
6 rows in set (0.00 sec)

MariaDB [test]> select * from linux WHERE user  LIKE 'user__';
+--------+--------+------+------+
| user   | passwd | sex  | age  |
+--------+--------+------+------+
| user11 | 1111   | girl | 31   |
+--------+--------+------+------+
1 row in set (0.00 sec)

MariaDB [test]> select * from linux WHERE user  LIKE 'user___';
+---------+--------+-----+------+
| user    | passwd | sex | age  |
+---------+--------+-----+------+
| user111 | 111111 | boy | 41   |
+---------+--------+-----+------+
1 row in set (0.00 sec)