如何在MySQL替换命令中使用Regexp? [英] How to Use Regexp in MySQL Replace Commands?

问题描述

我的目标是用通用链接替换数据库中的链接.我通常使用REPLACE命令来替换数据库中的字符串，但这一次我遇到了困难，因为要找到链接，我需要使用正则表达式，而这根本无法解决:

My goal is to replace the links in the database with a catchall link. I generally use the REPLACE command for replacing string in the database, but this time I am having difficulty because in order to find the links I need to use regular expressions, and that is simply not working out:

UPDATE node_revisions SET body = REPLACE ( `body` , 'http://.*.\pdf', '/migration-update' );

UPDATE node_revisions SET teaser = REPLACE ( `teaser` ,  'http://.*pdf', '/migration-update' );

这两个查询只是平淡无奇.

These two queries just fall flat.

在这种情况下，这里需要做什么?

What needs to be done here in this situation?

推荐答案

正如其他人已经提到的那样，您不能在MySQL中做到这一点.但是，这似乎是您需要做的一次性操作，因此我为您编写了一个快速而肮脏的小型php脚本来完成此工作.假设您的node_revisions表具有一个名为"id"的主键列.如果没有，请进行适当的编辑.另外，不要忘记在脚本顶部更改数据库主机，用户名，密码和数据库名称以匹配您的配置.

As others have mentioned already, you can't do this in MySQL. However, this appears to be a one time operation you need to do so I wrote you a quick and dirty little php script to do the job. It assumes your node_revisions table has a primary key column called 'id'. If not, edit appropriately. Also, don't forget to change the database host, username, password and database name at the top of the script to match your configuration.

<?php
$host = '127.0.0.1';
$username = 'root';
$password = 'password';
$database = 'test';

$conn = mysql_connect($host, $username, $password);

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}

if (!mysql_select_db($database)) {
    echo "Unable to select " . $database . ": " . mysql_error();
    exit;
}

$sql = "SELECT * FROM node_revisions";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
    $id = $row['id'];
    $body = $row['body'];
    $teaser = $row['teaser'];
    $body = preg_replace('/http:\/\/.*?\.pdf/', '/migration-update', $body);
    $teaser = preg_replace('/http:\/\/.*?\.pdf/', '/migration-update', $teaser);
    $sql = "UPDATE node_revisions set body='" . mysql_real_escape_string($body) . "', teaser='" . mysql_real_escape_string($teaser) . "' where id=" . $id;
    mysql_query($sql);
}

mysql_free_result($result);
mysql_close($conn);
?>

还请注意，我在正则表达式上使用了一个非贪婪修饰符，这样，如果您在body或teaser字段中有多个pdf url，则它们之间不会丢失所有内容.

Note also that I used a non-greedy modifier on the regular expressions so that if you have multiple pdf urls in a body or teaser field, you won't lose everything in between them.

这篇关于如何在MySQL替换命令中使用Regexp?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！