是否有相当于 PHP 的 preg_replace 的 MySQL？

Is there a MySQL equivalent of PHP's preg_replace?

提问by Travis

I have a to match a field in MySQL, for which I thought I could use a regular expression, but it appears that MySQL doesn't have the functionality I need to do the job. Here's the scenario:

我有一个匹配 MySQL 中的字段，我认为我可以使用正则表达式，但似乎 MySQL 没有我需要的功能来完成这项工作。这是场景：

I have a variable in PHP called $url. Let's say this variable is set as the string "/article/my-article/page/2". I also have a table of URLs in MySQL from which I would like to pull content. The URLs stored in my table, however, include wildcards.

我在 PHP 中有一个名为 $url 的变量。假设此变量设置为字符串“/article/my-article/page/2”。我在 MySQL 中还有一个 URL 表，我想从中提取内容。但是，存储在我的表中的 URL 包含通配符。

Previously, I had this set up so that the value stored in the table looked like this: "/article/%/page/%".

以前，我进行了此设置，以便存储在表中的值如下所示：“/article/%/page/%”。

With that configuration, I could just run:

使用该配置，我可以运行：

SELECT * FROM urls WHERE '$url' LIKE url

And this would match, which is the desired functionality.

这将匹配，这是所需的功能。

What I'd like to do now, is allow a more advanced wildcard, such that instead of "/article/%/page/%", my MySQL data could be "/article/{{slug}}/page/{{page_no}}".

我现在想做的是允许使用更高级的通配符，这样我的 MySQL 数据就可以不是“/article/%/page/%”，而是“/article/{{slug}}/page/{{ page_no}}”。

I want to create a SQL query that will match this data, using the same $url input. LIKE is no longer the correct comparison, since I'm not using the built-in "%" wildcard, but rather {{.*}}. Any ideas how to accomplish this?

我想使用相同的 $url 输入创建一个匹配此数据的 SQL 查询。LIKE 不再是正确的比较，因为我没有使用内置的“%”通配符，而是使用 {{.*}}。任何想法如何实现这一点？

采纳答案by Travis

I've found a solution. It's not ideal, but I'll put it in here in case a pure SQL solution never surfaces. I can leave the url field in MySQL equal to "/articles/%/page/%" and add another field called variables that stores a list of variable names to be used. That way I can use my original query, then replace the "%" characters in the return value with "{{variable}}" in PHP with sprintf after I've retrieved the data.

我找到了解决办法。这并不理想，但我会把它放在这里以防纯 SQL 解决方案永远不会出现。我可以让 MySQL 中的 url 字段等于“/articles/%/page/%”，并添加另一个名为 variables 的字段，用于存储要使用的变量名称列表。这样我就可以使用我的原始查询，然后在我检索数据后用 sprintf 在 PHP 中用“{{variable}}”替换返回值中的“%”字符。

Still, I'd like to see this solved in SQL if possible, since I think the concept is valuable.

尽管如此，如果可能的话，我还是希望在 SQL 中解决这个问题，因为我认为这个概念很有价值。

回答by sagi

There is a library of user defined functions that gives you preg_replace in MySQL: http://www.mysqludf.org/lib_mysqludf_preg/

有一个用户定义函数库可以在 MySQL 中为您提供 preg_replace：http://www.mysqludf.org/lib_mysqludf_preg/

回答by quickshiftin

It sounds like what you want to do is have the new syntax in the database where the URLs have placeholders you would pass to your php-based (sprintf) variable replacement code, but still be able to do the original comparisons to match the URL.

听起来您想要做的是在数据库中使用新语法，其中 URL 具有占位符，您将传递给基于 php 的 (sprintf) 变量替换代码，但仍然能够进行原始比较以匹配 URL。

If I understand correctly you want to take a new URL format

如果我理解正确，您想采用新的 URL 格式

/article/{{slug}}/page/{{page_no}}

and match it against something like

并将其与类似的东西相匹配

/article/my-article/page/2

The preg plugin sagi mentioned can do the substitution you need, which will turn one of your newly formatted URLs into the original format you used to determine the match using the LIKE syntax. The following query:

提到的 preg 插件 sagi 可以执行您需要的替换，这会将您新格式化的 URL 之一转换为您用于使用 LIKE 语法确定匹配的原始格式。以下查询：

SELECT PREG_REPLACE('/({{.*?}})/', '%', `url`) FROM urls;

Would turn the new url (/article/{{slug}}/page/{{page_no}}) into what it was originally

将新的 url (/article/{{slug}}/page/{{page_no}}) 变成原来的样子

/article/%/page/%

which can then be fed back through your original query, something like this:

然后可以通过您的原始查询反馈，如下所示：

SELECT * FROM urls
WHERE '/article/my-article/page/2' LIKE preg_replace('/({{.*?}})/', '%', `url`);

Some binary distributions like MAMP, XAMMP etc have the plugin already installed, but it isn't installed on a lot of systems like Macports / Ubuntu. Here are a couple of articles about installing the preg plugin. Hope it helps.

一些二进制发行版如 MAMP、XAMMP 等已经安装了插件，但它没有安装在很多系统上，如 Macports / Ubuntu。这里有几篇关于安装 preg 插件的文章。希望能帮助到你。

http://quickshiftin.com/blog/2011/04/installing-mysql-preg-plugin-osx-macports/

http://quickshiftin.com/blog/2011/04/installing-mysql-preg-plugin-osx-macports/

http://quickshiftin.com/blog/2011/12/installing-the-mysql-preg-plugin-on-ubuntu-with-apt-get/

http://quickshiftin.com/blog/2011/12/installing-the-mysql-preg-plugin-on-ubuntu-with-apt-get/

回答by billynoah

The user sagiabove mentions http://www.mysqludf.org/lib_mysqludf_preg/but as this answer is very old as are most of the tutorials, I wanted to expand on this for the sake of newcomers to this question.

上面的用户sagi提到了http://www.mysqludf.org/lib_mysqludf_preg/但由于这个答案和大多数教程一样非常古老，为了这个问题的新手，我想对此进行扩展。

Firstly, the library is really great and speaking from experience I can say it seems to have been maintained and is still working flawlessly in 2015.

首先，图书馆真的很棒，从经验来看，我可以说它似乎得到了维护，并且在 2015 年仍然可以完美运行。

To get it installed and working, I could only find some very dated tutorials so thought I would share what I did that worked for me installing latest stable release (v1.1) on Ubuntu 14.04:

为了安装和运行它，我只能找到一些非常过时的教程，所以我想我会分享我为在 Ubuntu 14.04 上安装最新稳定版本 (v1.1) 所做的工作：

apt-get update
apt-get install libpcre3-dev libmysqlclient-dev build-essential libmysqld-dev libpcre3-dev
wget https://github.com/mysqludf/lib_mysqludf_preg/archive/lib_mysqludf_preg-1.1.tar.gz
tar -xzf lib_mysqludf_preg-1.1.tar.gz
cd lib_mysqludf_preg-1.1
./configure
make  install
make installdb
service mysql restart

You should now have all the following functions available to you:

您现在应该可以使用以下所有功能：

lib_mysqludf_preg_info
preg_capture
preg_check
preg_replace
preg_rlike
preg_position

回答by Pierre-Olivier Vares

Starting from mysql 5.5, you can use RLIKE:

从 mysql 5.5 开始，您可以使用RLIKE：

• either store url in REGEXP-style, and query with

  SELECT * FROM urls WHERE '$url' RLIKE url;

• or keep it in LIKE-style, and make a replacement (% by .* , _ by .):

  SELECT * FROM urls WHERE '$url' RLIKE REPLACE(REPLACE(url, '%', '.*'), '_', '.');

  To be complete, you would have to do other replacements to escape chars that are regexp-significant : ? \ () [] ... (see php function preg_quote)

• 要么以 REGEXP 样式存储 url，然后使用

  SELECT * FROM urls WHERE '$url' RLIKE url;

• 或保持 LIKE 样式，并进行替换（% by .* , _ by .）：

  SELECT * FROM urls WHERE '$url' RLIKE REPLACE(REPLACE(url, '%', '.*'), '_', '.');

  为了完整起见，您必须进行其他替换以转义正则表达式重要的字符：？\ () [] ...（见 php 函数preg_quote）