9.7. Pattern Matching

9.7. 模式匹配

There are three separate approaches to pattern matching provided by PostgreSQL: the traditional SQL LIKE operator, the more recent SIMILAR TO operator (added in SQL:1999), and POSIX-style regular expressions. Aside from the basic “does this string match this pattern?” operators, functions are available to extract or replace matching substrings and to split a string at matching locations.

PostgreSQL提供了三种单独的模式匹配方法：传统的SQL LIKE运算符，SIMILAR TO运算符（在SQL：1999中添加）和POSIX样式的正则表达式。除了基本的“此字符串是否匹配此模式？”运算符，函数可用于提取或替换匹配的子字符串并在匹配的位置拆分字符串。

Tip

小贴士

If you have pattern matching needs that go beyond this, consider writing a user-defined function in Perl or Tcl.

如果有超出此范围的模式匹配需求，可以考虑使用Perl或Tcl编写用户自定义的函数。

 

Caution

注意

While most regular-expression searches can be executed very quickly, regular expressions can be contrived that take arbitrary amounts of time and memory to process. Be wary of accepting regular-expression search patterns from hostile sources. If you must do so, it is advisable to impose a statement timeout.

尽管大多数正则表达式搜索可以非常快速地执行，但却也可以构造能够耗费任意时间及内存去执行的正则表达式。因此从未知来源接受正则表达式的时候，一定要谨慎。如果必须这样做，则建议强制限制语句超时。

 

Searches using SIMILAR TO patterns have the same security hazards, since SIMILAR TO provides many of the same capabilities as POSIX-style regular expressions.

使用SIMILAR TO模式进行的搜索具有相同的安全隐患，因为SIMILAR TO提供了许多与POSIX样式正则表达式相同的功能。

 

LIKE searches, being much simpler than the other two options, are safer to use with possibly-hostile pattern sources.

与其他两个选项相比，LIKE搜索要简单得多，它与可能有害的模式源一起使用安全性会高一些。