当前位置:首页 > Mysql教程 > 列表

MySQL实现模糊查询(REGEXP,LIKE)有2种方式

发布:smiling 来源: PHP粉丝网  添加日期:2014-09-28 14:54:55 浏览: 评论:0 

在mysql中实现模糊查询有两种方法一种是LIKE/NOT LIKE,另一种是REGEXP/NOT REGEXP方法,下面我来给大家介绍它们的用法,希望此教程对各位同学会有所帮助.

一是用LIKE/NOT LIKE,二是用REGEXP/NOT REGEXP,或RLIKE/NOT RLIKE,它们是同义词.

第一种:标准的SQL模式匹配.

它有2种通配符:“_”和“%”,“_”匹配任意单个字符,而“%”匹配任意多个字符(包括0个),举例如下:

SELECT * FROM table_name WHERE column_name LIKE 'm%'; #查询某字段中以m或M开头的所有记录

SELECT * FROM table_name WHERE column_name LIKE '%m%'; #查询某字段中包含m或M的所有记录

SELECT * FROM table_name WHERE column_name LIKE '%m'; #查询某字段中以m或M结尾的所有记录

SELECT * FROM table_name WHERE column_name LIKE '_m_'; #查询某字段中3个字符且m或M在中间的所有记录

如果我们想查询包含通配符的字符串该怎么办呢?比如,50%或_get.

答案就是:转义,可以用来直接转义,或用ESCAPE定义转义字符来进行转义,都只是转义跟在后面的一个字符,例如,代码如下:

SELECT * FROM table_name WHERE column_name LIKE '%50%%'; /*第2个%被转义,查询某字段包含50%的所有记录*/

SELECT * FROM table_name WHERE column_name LIKE '%50/%%' ESCAPE '/'; #第2个%被转义

SELECT * FROM table_name WHERE column_name LIKE '%_get%' ESCAPE '/'; /*“_”被转义,查询某字段包含_get的所有记录*/

第二种:使用扩展正则表达式的模式匹配.

先来看下扩展正则表达式一些字符的含义:

“.”:匹配任意单个字符.

“?”:匹配前面的子表达式0次或1次.

“+”:匹配前面的子表达式1次或多次.

“*”:匹配前面的子表达式0次或多次,x*,表示0个或多个x字符;[0-9]*,匹配任何数量的数字.

“^”:表示匹配开始位置.

“$”:表示匹配结束位置.

“[]”:表示一个集合,[hi],表示匹配h或i;[a-d],表示匹配a、b、c、d中任一个.

“{}”:表示重复的次数。8{5},表示匹配5个8,即88888;[0-9]{5,11},表示匹配5到11个数字。

再来看个例子,代码如下:

SELECT * FROM table_name WHERE column_name REGEXP '^50%{1,3}';

/*查询某字段中以50%、50%%或50%%%开头的所有记录*/

方法三,如果要更高级的就是fulltext全文搜索了,下面我们通过实例来一步步把全文检索的过程解释清楚,首页我们建立表与初始化数据,Sql代码如下:

  1. CREATE TABLE IF NOT EXISTS `category` (         
  2.   `id` int(10) NOT NULL auto_increment,         
  3.   `fid` int(10) NOT NULL,         
  4.   `catname` char(255) NOT NULL,         
  5.   `addtime` char(10) NOT NULL,         
  6.   PRIMARY KEY  (`id`),         
  7.   FULLTEXT KEY `catname` (`catname`)         
  8. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
  9.         
  10. INSERT INTO `category` (`id`, `fid`, `catname`, `addtime`) VALUES        
  11. (1, 0, 'welcome to you!''1263363380'),         
  12. (2, 0, 'hello phpjs,you are welcome''1263363416'),         
  13. (3, 0, 'this is the fan site of you''1263363673');        
  14.      
  15. CREATE TABLE IF NOT EXISTS `category` ( `id` int(10) NOT NULL auto_increment, `fid` int(10) NOT NULL, `catname` char(255) NOT NULL, `addtime` char(10) NOT NULLPRIMARY KEY (`id`), FULLTEXT KEY `catname` (`catname`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; INSERT INTO `category` (`id`, `fid`, `catname`, `addtime`) VALUES (1, 0, 'welcome to you!''1263363380'), (2, 0, 'hello phpjs,you are welcome''1263363416'), (3, 0, 'this is the fan site of you''1263363673'); 

在具体实例之前,我们分析下msyql全文检索的语法:函数 MATCH() 对照一个文本集(包含在一个 FULLTEXT 索引中的一个或多个列的列集)执行一个自然语言搜索一个字符串。搜索字符串做为 AGAINST() 的参数被给定。搜索以忽略字母大小写的方式执行。说白了就是MATCH给定匹配的列(fulltext类型索引),AGAINST给定要匹配的字符串,多个用空格、标点分开,mysql会自动分隔.

SQL代码如下:SELECT * FROM `category` WHERE MATCH(catname) AGAINST('phpjs')

返回结果,代码如下:

id  fid  catname   addtime  

2   0    hello phpjs,you are welcome 1263363416

匹配出了含有phpjs关键字的行数据.

2、SQL代码如下:SELECT * FROM `category` WHERE MATCH (catname) AGAINST ('this') //phpfensi.com

按照上面的思路,第三行数据含有this,因此应该可以匹配出第三行数据的,但事实却奇怪得很,返回结果为空,为什么呢?

原来是mysql指定了最小字符长度,默认是4,必须要匹配大于4的才会有返回结果,可以用SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度,也可以在mysql配置文件my.ini 更改最小字符长度,方法是在my.ini 增加一行比如:ft_min_word_len = 2,改完后重启mysql即可。

3、这里我们要确定把最小字符改为2了,因为3行记录都有‘you’,因此心想,匹配‘you’就可以返回所有结果了.

SQL代码如下:SELECT * FROM `category` WHERE MATCH (catname) AGAINST ('you')  

返回结果还是为空,大跌眼镜了吧,这又是为什么呢?

原来mysql在集和查询中的对每个合适的词都会先计算它们的权重,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的集中,它有较低的语义值,否则,如果词是较少的,它将得到一个较高的权重,mysql默认的阀值是50%,上面‘you’在每个文档都出现,因此是100%,只有低于50%的才会出现在结果集中.

4、有人会想,我不去管权重大小,只要有匹配的就给我返回结果集中,那么该如何做呢?

mysql到 4.0.1 时,可以使用 IN BOOLEAN MODE 修饰语来执行一个逻辑全文搜索,SQL代码如下:

SELECT * FROM `category` WHERE MATCH(catname) AGAINST('you' IN BOOLEAN MODE)

Tags: MySQL模糊查询 REGEXP

分享到: