用MySQL正则表达式进行搜索

谁践踏了优雅 2023-07-11 05:54 45阅读 0赞

> 《MySQL必知必会》

正则表达式是用来匹配文本的特殊的串(字符集合)。  
所用到的三个表:customers、products、vendors  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA_size_16_color_FFFFFF_t_70 2]

1.  基本字符匹配: 关键字REGEXP后所跟的为正则表达式

#检索列prod_name包含文本'1000'的所有行
    mysql> SELECT prod_name
        -> FROM products
        -> WHERE prod_name REGEXP '1000'
        -> ORDER BY prod_name;
    +--------------+
    | prod_name    |
    +--------------+
    | JetPack 1000 |
    +--------------+
    1 row in set (0.00 sec)
    
    #用一个特殊字符'.'表示匹配任意一个字符
    mysql> SELECT prod_name
        -> FROM products
        -> WHERE prod_name REGEXP '.000'
        -> ORDER BY prod_name;
    +--------------+
    | prod_name    |
    +--------------+
    | JetPack 1000 |
    | JetPack 2000 |
    +--------------+
    2 rows in set (0.00 sec)

**关键字LIKE和REGEXP的区别：**  
LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它；REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。

mysql> SELECT prod_name
        -> FROM products
        -> WHERE prod_name LIKE '1000'
        -> ORDER BY prod_name;
    Empty set (0.00 sec)#SQL查询没有返回任何结果

**MySQL中的正则表达式匹配不区分大小写。但为区分大小写可使用关键字BINARY：**

WHERE prod_name REGEXP BINARY 'JetPack .000'

1.  进行OR匹配: 使用特殊字符 ‘|’

1.  匹配几个字符之一: 使用一组括号\[ \]，\[ \]是另一种形式的OR语句，但也有所区别。

1.  匹配范围: 集合可用来定义要匹配的一个或多个字符，在集合的开始出放置一个^可以否定一个字符集。可以用-来定义一个范围，如\[0-9\]、\[a-z\]。

1.  匹配特殊字符: 使用\\ \\为前导，如\\ \\ .(或-、|、\[ \])  
    \\ \\ 也用来引用元字符：

6.匹配字符类

<table> 
 <thead> 
  <tr> 
   <th>类</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>[:alnum:]</td> 
   <td>任意字母和数字（同[a-zA-Z0-9]）</td> 
  </tr> 
  <tr> 
   <td>[:alpha:]</td> 
   <td>任意字符（同[a-zA-Z]）</td> 
  </tr> 
  <tr> 
   <td>[:blank:]</td> 
   <td>空格和制表（同[\ \t]）</td> 
  </tr> 
  <tr> 
   <td>[:cntrl:]</td> 
   <td>ASCLL控制字符（ASCLL0到31和127）</td> 
  </tr> 
  <tr> 
   <td>[:digit:]</td> 
   <td>任意数字（同[0-9]）</td> 
  </tr> 
  <tr> 
   <td>[:graph:]</td> 
   <td>与[:print:]相同，但不包括空格</td> 
  </tr> 
  <tr> 
   <td>[:lower:]</td> 
   <td>任意小写字母（同[a-z]）</td> 
  </tr> 
  <tr> 
   <td>[:print:]</td> 
   <td>任意可打印字符</td> 
  </tr> 
  <tr> 
   <td>[:punct:]</td> 
   <td>既不在[:alnum:]又不在[:cntrl:]中的任意字符</td> 
  </tr> 
  <tr> 
   <td>[:space:]</td> 
   <td>包括空格在内的任意空白字符 同（\ \f\ \n\ \r\ \t\ \v）</td> 
  </tr> 
  <tr> 
   <td>[:upper:]</td> 
   <td>任意大写字母（同[A-Z]）</td> 
  </tr> 
  <tr> 
   <td>[:xdigit:]</td> 
   <td>任意十六进制数字（同[a-fA-F0-9]）</td> 
  </tr> 
 </tbody> 
</table>

1.  匹配多个实例:使用重复元字符对要匹配的数目进行更强的控制。

<table> 
 <thead> 
  <tr> 
   <th>元字符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>*</td> 
   <td>0个或多个匹配</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>1个或多个匹配（等于{1, }）</td> 
  </tr> 
  <tr> 
   <td>？</td> 
   <td>0个或1个匹配（等于{0, 1}）</td> 
  </tr> 
  <tr> 
   <td>{n}</td> 
   <td>指定数目的匹配</td> 
  </tr> 
  <tr> 
   <td>{n, }</td> 
   <td>不少于指定数目的匹配</td> 
  </tr> 
  <tr> 
   <td>{n, m}</td> 
   <td>匹配数目的范围（m不超过255）</td> 
  </tr> 
 </tbody> 
</table>

#\$匹配(, [0-9]匹配任意数字, sticks?匹配stick和sticks, \$匹配)
    mysql> SELECT prod_name
        -> FROM products
        -> WHERE prod_name REGEXP '\$[0-9] sticks?\$'
        -> ORDER BY prod_name;
    +----------------+
    | prod_name      |
    +----------------+
    | TNT (1 stick)  |
    | TNT (5 sticks) |
    +----------------+
    2 rows in set (0.00 sec)
    
    #{4}确切要求它前面的字符出现4次
    #等价于'[0-9][0-9][0-9][0-9]'
    mysql> SELECT prod_name
        -> FROM products
        -> WHERE prod_name REGEXP '[[:digit:]]{4}'
        -> ORDER BY prod_name;
    +--------------+
    | prod_name    |
    +--------------+
    | JetPack 1000 |
    | JetPack 2000 |
    +--------------+
    2 rows in set (0.00 sec)

1.  定位符: 以上所有例子都是匹配一个串任意位置的文本。为了匹配特定位置的文本，需要使用定位符。

<table> 
 <thead> 
  <tr> 
   <th>元字符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>^</td> 
   <td>文本的开始</td> 
  </tr> 
  <tr> 
   <td>$</td> 
   <td>文本的结尾</td> 
  </tr> 
  <tr> 
   <td>[[:&lt;:]]</td> 
   <td>词的开始</td> 
  </tr> 
  <tr> 
   <td>[[:&gt;:]]</td> 
   <td>词的结尾</td> 
  </tr> 
 </tbody> 
</table>

#找出以一个数字或小数点开始的所有产品, 也可用'[0-9]\\.]'
    mysql> SELECT prod_name
        -> FROM products
        -> WHERE prod_name REGEXP '^[[:digit:]\\.]'
        -> ORDER BY prod_name;
    +--------------+
    | prod_name    |
    +--------------+
    | .5 ton anvil |
    | 1 ton anvil  |
    | 2 ton anvil  |
    +--------------+
    3 rows in set (0.00 sec)

**通过用^开始每个表达式，用$结束每个表达式，可使REGEXP的作用与LIKE一样。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200304130304282.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200304130337560.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200304130356797.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc5MDM2MA==,size_16,color_FFFFFF,t_70