替换MySQL的URL模式

Question

I'm trying to figure out how to use a regex search on a MySQL column to update some data.

The problem is I'm trying to rename part of a URL (i.e a directory).

The table looks something like this (although it's just an example, the actual data is arbitrary):

myTable:

| user_name     | URL                                                       |
| ------------- |:---------------------------------------------------------:|
| John          | http://example.com/path/to/Directory/something/something  |
| Jane          | http://example.com/path/to/Directory/something/else       | 
| Jeff          | http://example.com/path/to/Directory/something/imgae.jpg  |

I need to replace all the URLs that have "path/to/Directory/" to "path/to/anotherDirectory/" while keeping the rest of URL intact.

So the result after the update should look like this:

| user_name     | URL                                                              |
| ------------- |:----------------------------------------------------------------:|
| John          | http://example.com/path/to/anotherDirectory/something/something  |
| Jane          | http://example.com/path/to/anotherDirectory/something/else       | 
| Jeff          | http://example.com/path/to/anotherDirectory/something/imgae.jpg  |

At the moment, the only way I could figure out how to do it is using a combination of regex quires to check for the directory, and then loop over it and change the URL, like this:

$changeArr = $db->query("SELECT URL FROM myTable WHERE URL REGEXP 'path/to/Directory/.+'");

$URLtoChange = "path/to/Directory/";
$replace     = "path/to/anotherDirectory/"
foreach ($changeArr as $URL) {
   $replace = str_replace($URLtoChange, $replace, $URL);
   $db->query("UPDATE myTable SET URL = :newURL WHERE URL = :URL", array("URL"=>$URL,"newURL"=>$replace));
}

This seems to work pretty well, however with such with a big table it can be pretty heavy on performance.

I was wondering if there's a more efficient way to do this? Perhaps with some sort of regex replace in a mySQL query.

Answer 1

Just use the REPLACE() function:

UPDATE myTable
SET URL = REPLACE(url, 'path/to/Directory/', 'path/to/anotherDirectory/')
WHERE URL LIKE '%path/to/Directory/%'