在PHP中编写MySQL数据库查找/替换脚本

I'm currently in the process of redeveloping my bespoke website to a WordPress-driven CMS.

我目前正在将我的定制网站重新开发为WordPress驱动的CMS。

The website I've been working on is simply my existing URL plus /dev/, i.e. http://my.website.com/dev/.

我一直在研究的网站只是我现有的网址加/ dev /,即http://my.website.com/dev/。

I'll be moving this website to http://my.website.com over the weekend, and as such will need to remove all references to the /dev/ URL.

我将在周末将此网站移至http://my.website.com,因此需要删除对/ dev / URL的所有引用。

What I'd like to do is, basically, a "find and replace" for /dev on my database. I can see exactly which tables have this value in, but naturally as per a WordPress install, quite a lot of these fields are serialized data - which a straightforward dump > open with notepad++ > find & replace will break.

我想做的是,基本上,我的数据库上的/ dev的“查找和替换”。我可以确切地看到哪些表具有此值,但是自然地按照WordPress安装,很多这些字段都是序列化数据 - 使用notepad ++> find&replace打开的直接转储将会中断。

The code I've developed for this purpose is here:

我为此目的开发的代码在这里:

<?php

$look_at[] = array( "table" => "wp_options", "fields" => array( "option_value" ), "id_field" => "option_id" );
$look_at[] = array( "table" => "wp_postmeta", "fields" => array( "meta_value" ), "id_field" => "meta_id" );
$look_at[] = array( "table" => "wp_posts", "fields" => array( "post_content", "guid" ), "id_field" => "ID" );
$look_at[] = array( "table" => "wp_sfmeta", "fields" => array( "meta_value" ), "id_field" => "meta_id" );
$look_at[] = array( "table" => "wp_sfoptions", "fields" => array( "option_value" ), "id_field" => "option_id" );
$look_at[] = array( "table" => "wp_sferrorlog", "fields" => array( "error_text" ), "id_field" => "id" );

for ( $i = 0; $i < sizeof ( $look_at ); $i++ ) {
    foreach( $look_at[$i]["fields"] as $field ) {

        $sql = 'SELECT `' . $field . '`, `' . $look_at[$i]["id_field"] . '` FROM  `' . $look_at[$i]["table"] . '`;';
        $res = mysql_query( $sql );

        while ( $row = mysql_fetch_assoc( $res ) ) {

            $table = $look_at[$i]["table"];
            $id_field = $look_at[$i]["id_field"];
            $old_val = $row[$field];
            $id = $row[$id_field];

            $unserialized_value = @unserialize( $old_val );

            if ( $old_val === 'b:0;' || $unserialized_value !== false )
                $new_val = serialize( str_replace( array( "/dev/", "/dev" ), array( "/", "" ), $unserialized_value ) );
            else
                $new_val = str_replace( array( "/dev/", "/dev" ), array( "/", "" ), $old_val );

            $update_array[] = array( "id_field" => $id_field, "id" => $id, "table" => $table, "key" => $key, "old_val" => $old_val, "new_val" => $new_val );

        }

    }
}

for ( $i = 0; $i < sizeof( $update_array ); $i++ ) {
    if ( $update_array[$i]["old_val"] !== $update_array[$i]["new_val"] )
        $updated_sql .= 'UPDATE ' . $update_array[$i]["table"] . ' SET `' . $update_array[$i]["key"] . '` = \'' . $update_array[$i]["new_val"] . '\' WHERE `' . $update_array[$i]["id_field"] . '` = \'' . $update_array[$i]["id"] . '\';';
}

mysql_query( $updated_sql );

?>

An example of the serialized data:

序列化数据的示例:

a:6:{s:5:"width";s:3:"400";s:6:"height";s:3:"530";s:14:"hwstring_small";s:22:"height='96' width='72'";s:4:"file";s:30:"2011/12/Amazonas-English-1.jpg";s:5:"sizes";a:13:{s:9:"thumbnail";a:3:{s:4:"file";s:30:"Amazonas-English-1-125x165.jpg";s:5:"width";s:3:"125";s:6:"height";s:3:"165";}s:6:"medium";a:3:{s:4:"file";s:30:"Amazonas-English-1-339x450.jpg";s:5:"width";s:3:"339";s:6:"height";s:3:"450";}s:5:"large";s:0:"";s:14:"post-thumbnail";a:3:{s:4:"file";s:30:"Amazonas-English-1-125x165.jpg";s:5:"width";s:3:"125";s:6:"height";s:3:"165";}s:23:"indexleft-species-thumb";a:3:{s:4:"file";s:30:"Amazonas-English-1-200x265.jpg";s:5:"width";s:3:"200";s:6:"height";s:3:"265";}s:13:"species-thumb";a:3:{s:4:"file";s:30:"Amazonas-English-1-288x381.jpg";s:5:"width";s:3:"288";s:6:"height";s:3:"381";}s:17:"indexheader-thumb";a:5:{s:4:"file";s:30:"Amazonas-English-1-400x300.jpg";s:5:"width";s:3:"400";s:6:"height";s:3:"300";s:4:"path";s:38:"2011/12/Amazonas-English-1-400x300.jpg";s:3:"url";s:88:"http://www.xxxxxxxxxxx.com/dev/wp-content/uploads/2011/12/Amazonas-English-1-400x300.jpg";}s:14:"random-thumb-1";a:3:{s:4:"file";s:28:"Amazonas-English-1-56x75.jpg";s:5:"width";s:2:"56";s:6:"height";s:2:"75";}s:14:"random-thumb-2";a:3:{s:4:"file";s:29:"Amazonas-English-1-75x100.jpg";s:5:"width";s:2:"75";s:6:"height";s:3:"100";}s:14:"random-thumb-3";a:3:{s:4:"file";s:29:"Amazonas-English-1-94x125.jpg";s:5:"width";s:2:"94";s:6:"height";s:3:"125";}s:14:"random-thumb-4";a:3:{s:4:"file";s:30:"Amazonas-English-1-113x150.jpg";s:5:"width";s:3:"113";s:6:"height";s:3:"150";}s:14:"random-thumb-5";a:3:{s:4:"file";s:30:"Amazonas-English-1-132x175.jpg";s:5:"width";s:3:"132";s:6:"height";s:3:"175";}s:13:"d4p-bbp-thumb";s:0:"";}s:10:"image_meta";a:10:{s:8:"aperture";s:1:"0";s:6:"credit";s:0:"";s:6:"camera";s:0:"";s:7:"caption";s:0:"";s:17:"created_timestamp";s:1:"0";s:9:"copyright";s:0:"";s:12:"focal_length";s:1:"0";s:3:"iso";s:1:"0";s:13:"shutter_speed";s:1:"0";s:5:"title";s:0:"";}}

ADDITIONAL EDIT

Unfortunately, there are other instances of /dev/ in other serialized arrays, such as this example:

不幸的是,在其他序列化数组中还有/ dev /的其他实例,例如:

'a:1:{i:0;a:5:{s:4:"type";s:5:"image";s:3:"loc";s:107:"/home/xxxxx/domains/xxxxxxxxx.com/public_html/dev/wp-content/sp-resources/forum-image-uploads/matt/2012/01/";...

Or,

a:1:{i:0;a:5:{s:4:"data";s:88:"Your search - <b>link:http://www.xxxxxxxxx.com/dev/</b> - did not match any documents. ";...

a:1:{i:0; a:5:{s:4:“data”; s:88:“您的搜索 - 链接:http://www.xxxxxxxxx.com/dev/ - 与任何文件都不匹配。“; ...

As such, I don't think a simple preg_replace (or callback) will do the trick, but I guess an advanced one might?

因此,我不认为一个简单的preg_replace(或回调)会做到这一点,但我想一个先进的可能?

My questions are:

我的问题是:

Is there a simpler way of doing this?!

有更简单的方法吗?!

Will the above code run into any problems?

以上代码会遇到任何问题吗?

I'm awful at forseeing problems with my code (bad programmer, I do apologise) and as such a little apprehensive about running tests with this code.

我很害怕我的代码遇到问题(糟糕的程序员,我道歉),因此对使用此代码运行测试有点担心。

FINAL EDIT: WORKING CODE

最终编辑:工作代码

Because my SQL dump was nearly 100mb, I had to use WAMP with unlimited memory.

因为我的SQL转储接近100mb,所以我不得不使用无限内存的WAMP。

<?php
    error_reporting(E_ALL);
    ini_set('display_errors', 'On');
    ini_set('memory_limit', '-1');

    $handle = @fopen("amend-this.sql", "r");
    if ($handle) {
        while (($buffer = fgets($handle, 4096)) !== false) {
          $newLine = preg_replace_callback('@s:(\d+)(:\\\"[^"]*www.seriouslyfish\.com)/dev@', create_function('$matches', 'return \'s:\'.($matches[1] - 4).$matches[2];'), $buffer);
          $newLine = preg_replace_callback('@s:(\d+)(:\\\"[^\\\"]*/home/sfish/domains/seriouslyfish\.com/public_html)/dev@', create_function('$matches', 'return \'s:\'.($matches[1] - 4).$matches[2];'), $newLine);
          $newLine = str_replace('http://dunc.seriouslyfish.com/dev/', 'http://www.seriouslyfish.com/', $newLine);
          $newLine = str_replace('http://www.seriouslyfish.com/dev/', 'http://www.seriouslyfish.com/', $newLine);
          $newLine = str_replace('/dev', '', $newLine);
          file_put_contents( "amended.sql", $newLine, FILE_APPEND );
        }
        fclose($handle);
    }
?>

This code put my new SQL file into the same directory (X:\wamp\www) for me to manipulate further.

此代码将我的新SQL文件放入同一目录(X:\ wamp \ www),以便我进一步操作。

I had a few issues with data-repetition, and there were 67 instances of /dev still in the file for some reason but I used Notepad++ and WinMerge to sort all of this out and in the end it took me around 45 minutes to search/replace a database of over 90 million characters.

我有一些数据重复的问题,由于某些原因,文件中还有67个/ dev实例,但我使用Notepad ++和WinMerge对所有这些进行排序,最后花了大约45分钟搜索/替换超过9000万个字符的数据库。

3 个解决方案

#1

When I had the same problem I ran a mysqldump of the database, then opened in a text editor and just search/replaced the values, before using the SQL to create the new database. Quite simple, surprisingly fast,especially for a one off.

当我遇到同样的问题时,我运行了数据库的mysqldump,然后在文本编辑器中打开,只搜索/替换值,然后使用SQL创建新数据库。非常简单,令人惊讶的快,尤其是一次性。

As pointed out, you have the problem with serialized data, so you could do a similar thing with a simple PHP file:

正如所指出的,你有序列化数据的问题,所以你可以用一个简单的PHP文件做类似的事情:

<?php
$handle = @fopen("/tmp/dump.sql", "r");
if ($handle) {
    while (($buffer = fgets($handle, 4096)) !== false) {
      $newLine = preg_replace_callback('@s:(\d+)(:\\\"[^"]*xxxxxxxxxxx\.com)/dev@', create_function('$matches', 'return \'s:\'.($matches[1] - 4).$matches[2];'), $buffer);
      $newLine = preg_replace_callback('@s:(\d+)(:\\\"[^\\\"]*xxxxxxxxxxx\.com/public_html)/dev@', create_function('$matches', 'return \'s:\'.($matches[1] - 4).$matches[2];'), $newLine);
      $newLine = str_replace('http://www.xxxxxxxxxxx.com/dev/', 'http://www.xxxxxxxxxxx.com/', $newLine);
      echo $newLine;
    }
    fclose($handle);
}
?>

Note: this works on a mysqldump, if you're testing, you'll need to remove the \\\ before the "s in the preg_replace_callbacks - this is just mysqldump escaping quotes.

注意:这适用于mysqldump,如果你正在测试,你需要在preg_replace_callbacks中的s之前删除\\\ - 这只是mysqldump转义引号。

Also Note: There are two preg replaces (one for normal URLs and one for server paths), and one str replace for standard URLs left over.

另请注意:有两个preg替换(一个用于普通URL,一个用于服务器路径),一个str替换剩余的标准URL。

#2

There is the serialization fixer wordpress plugin which does it for the non-programmer: http://davidcoveney.com/575/php-serialization-fix-for-wordpress-migrations/

有一个序列化修复程序wordpress插件,它为非程序员执行:http://davidcoveney.com/575/php-serialization-fix-for-wordpress-migrations/

You can also do it with PHP.

你也可以用PHP来做。

Also, here is a sample MySQL code which does it: http://data.stackexchange.com/drupal%20answersmeta/query/80128/sql-search-and-replace

此外,这是一个示例MySQL代码:http://data.stackexchange.com/drupal%20answersmeta/query/80128/sql-search-and-replace

Take care, since this is a very dangerous tool.

小心,因为这是一个非常危险的工具。

#3

Couldn't you just use WP CLI for this?

难道你不能只使用WP CLI吗?

wp search-replace https://example.dev https://example.com

#1