Geek Answers Handbook

How to match MySQL records using regular expressions?

I am creating a news system that uses unique slugs to identify an article. When creating a new article, I need to make sure that the unique slug is not yet in use. Therefore, if two articles have the same name, they will thus generate the same. I want to add a number to the end of the bullet in case it is used.

Same:

  • some very interesting articles
  • some-very-interesting-article-1
  • some-very-interesting-article-2

And so on. Therefore, in my test case, I select all entries from the database that exactly match "some-really-interesting-article" with 0 or more "-number". So in this case I will return 3 lines, so the next slug will be "some-really-interesting-article-3".

This works fine, except that my regex behaves in a peculiar way (or probably very normal, and I just pull in the regex) and also returns strings with partial matches. Therefore, if I search for “really interesting”, it will pass.

SELECT id, title, slug FROM news WHERE slug RLIKE '([[:<:]]some-really-interesting-article[[:>:]][-\d]*)'

So, as I said, if I run the above regular expression with "some-really-interesting", it will return all 3 lines.

Please tell me that I am blatantly and reluctantly doing wrong. Thanks.

+4
source share
3 answers

How about this?

 "^some-really-interesting-article(-[[:digit:]]+)?$"

Hope it works :)

+1
source

Try

 WHERE slug RLIKE '(^some-really-interesting-article(-[0-9]+)?$)'
+1
source

As already noted, this will work:

 SELECT id, title, slug FROM news WHERE slug RLIKE '^some-really-interesting-article(-[0-9]+)?$'

However, MySQL is not smart enough to use indexes effectively with RLIKE . (If you have an index in the slug column, it may try to use it, but will have to perform a full index scan.) It turns out, however, that you can use the redundant LIKE clause to help MySQL process the query more efficiently, for example:

 SELECT id, title, slug FROM news WHERE slug LIKE 'some-really-interesting-article%' AND slug RLIKE '^some-really-interesting-article(-[0-9]+)?$'

A LIKE request may coincide with some false positives, such as some-really-interesting-article-about-something-else-entirely , but RLIKE will supplant them. Of course, if you don't care about such false positives, you can completely eliminate RLIKE .

Of course, another possibility would be to create a unique index in the slug column (which you really should have anyway) and just keep increasing the pool and trying to insert the record until INSERT succeeds. Of course, this also means that you should check the error code and message to find out why it fails if this happens. (If a INSERT fails due to a double value, the error code should be 1062 , and the error message should contain the name of the duplicate.)

In any case , the SQLize link is here if you want to play with the query; I left some comments in it.

0
source

More articles:


All Articles