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
结果一目了然。