MySQL中的正则表达式错误(在PHP中使用相同的正则表达式) [英] Regex errors in MySQL (same regex works in PHP)

问题描述

我正在使用搜索功能来接受用户输入并搜索mysql字段 想法是分解用户搜索的单词，并在字段中查找每个术语 有问题.

I'm working on a search function to take user input and search a mysql fields The idea is to split up the words a user searches for and look for each term in the field(s) in question.

用于构建我正在使用的正则表达式的代码是从一个工作正常的PHP脚本复制的(正则表达式似乎可以在PHP上正常工作，因此我怀疑这与正则表达式的风格有所不同)

The code used to build the regex I am using was copied from a working pure PHP script (the regex seems to work fine with PHP, so I suspect it is a difference in regex flavor)

如果这是一个明显的问题，请原谅我，但仍然对正则表达式有所了解.

Forgive me if this is an obvious problem, still getting a feel for regex.

首先，我将向您展示我运行的查询和出现的错误

First I will show you the queries I run and the errors I get

"SELECT * from `images` WHERE ( `name` REGEXP '^(?=.*gallardo).*$' OR `meta` REGEXP '^(?=.*gallardo).*$' ) ORDER BY `changestamp` DESC

正则表达式中出现错误重复操作符操作数无效""

Got error 'repetition-operator operand invalid' from regexp"

SELECT * from `images` WHERE ( `name` REGEXP '^(?=.*gallardo)(?=.*lambo).*$' OR `meta` REGEXP '^(?=.*gallardo)(?=.*lambo).*$' ) ORDER BY `changestamp` DESC

给出相同的错误

要编译此正则表达式，我接受用户提交的输入，例如"gallardo lambo" 并运行此PHP过程

to compile this regex, I take user submitted input like "gallardo lambo" and run this PHP procedure

if(isset($_GET['keyword'])){

        $searchterms = explode(' ',$_GET['keyword']);

        $regstr = '^';

        foreach($searchterms as $i => $v)
        {
            if($v)
                $regstr .= '(?=.*'.preg_replace('/[-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/', "\\$&", $v).')'; 
        }

        $regstr .= '.*$';

    }

然后将其放在查询中

"( `name` REGEXP '$regstr' OR `meta` REGEXP '$regstr' )"

当我将此方法与php的preg_match()一起使用时，它似乎可以工作.

When I use this method with php's preg_match(), it seems to work.

有见识吗?随时告诉我，我不知道自己在做什么.

Any insight? Feel free to tell me that I have no idea what I'm doing.

谢谢！

推荐答案

MySQL正则表达式比PHP更受限制-它们不支持向后引用或超前.参见手册.

MySQL regex are more limited than PHP - they don't support back references or lookahead. See the manual.

您可能想研究全文搜索.

这篇关于MySQL中的正则表达式错误(在PHP中使用相同的正则表达式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！