替换MySQL的URL模式

我正在试图弄清楚如何在MySQL列上使用正则表达式搜索来更新某些数据。 </ p>

问题是我正在尝试重命名URL的一部分(即目录)。</ p>

表格看起来像这样(尽管它是 举个例子,实际数据是任意的):</ p>

myTable:</ p>

  |  user_name | 网址| 
| ------------- |:----------------------------------- ----------------------:|
| 约翰| http://example.com/path/to/Directory/something/something |
| 简| http://example.com/path/to/Directory/something/else |

| 杰夫| http://example.com/path/to/Directory/something/imgae.jpg |
</ code> </ pre>

我需要替换所有包含“path / to”的网址 / Directory /“to”path / to / anotherDirectory /“同时保持URL的其余部分保持不变。</ p>

因此更新后的结果应如下所示:</ p>
\ ñ

 <代码> |  user_name | 网址| 
| ------------- |:----------------------------------- -----------------------------:|
| 约翰| http://example.com/path/to/anotherDirectory/something/something |
| 简| http://example.com/path/to/anotherDirectory/something/else |

| 杰夫| http://example.com/path/to/anotherDirectory/something/imgae.jpg |
</ code> </ pre>

目前,我唯一可以弄清楚如何 它是使用正则表达式quires的组合来检查目录,然后循环它并更改URL,如下所示:</ p>

  $ changeArr = $ db-&gt;  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-&gt; query(”UPDATE myTable SET URL =:newURL WHERE URL =: 网址“,数组(”网址“=&gt; $ URL,”newURL“=&gt; $ replace));
}
</ code> </ pre>

这似乎很漂亮 好吧,不过有这样一个大桌子,它的表现可能相当沉重。</ p>

我想知道是否有更有效的方法来做到这一点? 也许在mySQL查询中使用某种正则表达式替换。</ p>
</ div>

展开原文

原文

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.

1个回答



只需使用 REPLACE()</ code> 功能:</ p>

  UPDATE myTable 
SET URL = REPLACE( url,'path / to / Directory /','path / to / anotherDirectory /')
WHERHER URL LIKE'%path / to / Directory /%'
</ code> </ pre>
</ div>

展开原文

原文

Just use the REPLACE() function:

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

douchengchen7959
douchengchen7959 谢谢! 工作完美! :)
大约 6 年之前 回复
doutusheng5879
doutusheng5879 可能,没有。
大约 6 年之前 回复
dtxa49711
dtxa49711 我是否需要在URL中str_replace“/”以转发LIKE的“\ /”版本?
大约 6 年之前 回复
dorpbn1027
dorpbn1027 你打败了我。 是关于发布相同的事情。
大约 6 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐