mysql中数据有两个运算符来提供字符字符串查询匹配的,分别是like和regexp,下面来看一下。
mysql> select * from tmp;
+------+----------+
| id | name |
+------+----------+
| 2 | lisi |
| 1 | zhangsan |
| 3 | NULL |
+------+----------+
3 rows in set (0.00 sec)
对于like,'%'匹配任何数目的字符,甚至包括0字符,'_'只能匹配一个字符。
mysql> select * from tmp where name like 'li';
Empty set (0.00 sec)
直接匹配,不适用匹配符这样会失败。
mysql> select * from tmp where name like 'l%';
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tmp where name like 'lis%';
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tmp where name like 'z%';
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql>
%匹配任意数目的字符。
mysql> select * from tmp where name like 'lis_';
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tmp where name like 'li_';
Empty set (0.00 sec)
mysql>
'_'只能匹配一个字符。
对于regexp运算符,先看看不带任何匹配符的情况。
mysql> select * from tmp where name regexp 'li';
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tmp where name regexp 'zh';
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
也是可以进行匹配的,这和like运算符不一样。regexp有一些通配符来提供匹配的,类似于正则表达式。
'^'匹配以该字符后面的字符开头的字符串。
mysql> select * from tmp where name regexp '^z';
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> select * from tmp where name regexp '^w';
Empty set (0.00 sec)
'$'匹配以该字符后面的字符结尾的字符串。
mysql> select * from tmp where name regexp 'i$';
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.00 sec)
注意'^'和'$'的位置放置。
'.'用来匹配任何一个字符,因为regexp本身就有匹配的字符的能力,我觉得这个有点像鸡肋了。
'[...]'用来匹配里面的任何字符。
mysql> select * from tmp where name regexp '[lz]';
+------+----------+
| id | name |
+------+----------+
| 2 | lisi |
| 1 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
'*'匹配0个或多个在它前面的字符,0个也行,那不是匹配所有,即所以的记录都行。
mysql> select * from tmp where name regexp '0*';
+------+----------+
| id | name |
+------+----------+
| 2 | lisi |
| 1 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
mysql> pipei
结果一目了然。