使用正则表达式选择字段的一部分 [英] Selecting part of a field with a regex

问题描述

我有一个表，其中一个第三方组件存储了URL，我只想从该URL中获取id参数.

I've a table where a 3rd party component stores urls, i would like to get only the id parameter from this url.

使用PHP，我可以这样做:

With PHP i can do it like this:

$subject = "index.php?option=com_content&catid=2&id=456&view=article"; //mysql query result
$pattern = '*[&?]id=([0-9]+)*'; //matches either ?id=456 or &id=456
preg_match($pattern, $subject, $matches);
echo $matches[1];//prints 456

匹配的数字将成为新查询的一部分:

The number matched would be part of a new query:

SELECT name FROM table1 WHERE id=the_match

现在，我认为直接通过mysql进行操作会快很多，例如

Now, i think it would be a lot faster to do it directly via mysql, something like

SELECT name FROM table1 WHERE id = (SELECT REGEX_MATCH('*[&?]id=([0-9]+)*', '$1') FROM table2 WHERE uniquefield1 = 'fred')

显然SELECT REGEX_MATCH('*[&?]id=([0-9]+)*', '$1') FROM table2 WHERE uniquefield1 = 'fred')是完全发明的，只是说我想从正则表达式中选择第一个匹配的组，并使用它使WHERE子句起作用.

Obviously SELECT REGEX_MATCH('*[&?]id=([0-9]+)*', '$1') FROM table2 WHERE uniquefield1 = 'fred') is completely invented, just to say that i want to select the first group matched from regex and use it to make the WHERE clause work.

是否可以使用MySQL做类似的事情?

Is it possible to do something like this with MySQL?

推荐答案

没有，伤心地说，MySQL没有办法正则表达式应用到列的内容在SELECT子句中，只有一个WHERE子句.

No, sad to say MySQL doesn't have a way to apply a regex to a column's contents in a SELECT clause, only a WHERE clause.

但是您可以使用普通的(非正则表达式)字符串操作函数来执行此操作.如果包含与号分隔符的参数字符串的列名为url，则可以使用此细字符串表达式获取ID号，该表达式将找到您的ID号.

But you can use ordinary (non-regex) string manipulation functions to do this. If the column containing your ampersand-separated parameter string is named url, you can get the id number with this fine string expression, which finds your id number.

  CAST(RIGHT(url, LENGTH(url) - 3 - LOCATE('&id=', url)) AS SIGNED INTEGER)

因此，如果要从table1的url列中列出id值，则可以使用此SELECT查询.

So, if you want a list of id values from the url columns of table1, you could use this SELECT query.

SELECT CAST(RIGHT(url, LENGTH(url) - 3 - 
                       LOCATE('&id=', url)) AS SIGNED INTEGER) AS id
  FROM table1
 WHERE url REGEXP '&id=[0-9]+'

如您所见，它使用regexp搜索功能来定位适当的行.

As you can see this uses the regexp search function to locate the appropriate rows.

这没有什么快的.正则表达式匹配无法利用MySQL索引.如果您可以选择使用预先提取的id列加载表，那么在表变大时进行搜索会更好.

There is nothing fast about this. Regexp matching can't exploit a MySQL index. If you have the choice of loading your table with the id column pre-extracted you'll be much better off searching when your table gets big.

这篇关于使用正则表达式选择字段的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！