MySQL与PHP的preg_replace是否等效? [英] Is there a MySQL equivalent of PHP's preg_replace?

问题描述

我有一个与MySQL中的字段匹配的字段，我以为我可以使用正则表达式，但是看来MySQL不具备执行此工作所需的功能.这是场景:

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:

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

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.

以前，我已经进行了此设置，以便存储在表中的值看起来像这样:"/article/％/page/%".

Previously, I had this set up so that the value stored in the table looked like this: "/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.

我现在想做的是允许使用更高级的通配符，这样，我的MySQL数据可能不是"/article/％/page/％"，而是"/article/{{slug}}/page" /{{page_no}}".

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}}".

我想使用相同的$ url输入创建一个将与此数据匹配的SQL查询. LIKE不再是正确的比较，因为我不是在使用内置的％"通配符，而是在使用{{.*}}.有什么想法可以做到这一点吗?

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?

推荐答案

我找到了一个解决方案.这不是理想的选择，但我将其放在此处，以防纯SQL解决方案永不浮出水面.我可以在MySQL中将url字段保留为等于"/articles/％/page/％"，并添加另一个称为变量的字段，该字段存储要使用的变量名列表.这样，我可以使用原始查询，然后在检索数据后，在sprintf中用sprintf将返回值中的％"字符替换为"{{variable}}".

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中解决此问题，因为我认为这个概念很有价值.

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

这篇关于MySQL与PHP的preg_replace是否等效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！