我目前正在将我的定制网站重新开发为WordPress驱动的CMS。
我一直在工作的网站只是我现有的URL加/dev/(即http://my.website.com/dev/)。
我将在周末将这个网站移至http://my.website.com，因此将需要删除所有对/dev/ URL的引用。
基本上，我想做的是对数据库中的/dev进行“查找并替换”。我可以确切地看到哪些表具有此值，但是按照WordPress的安装，很自然地，这些字段中有很多是序列化数据的，简单的dump> open with notepad++> find & replace将被破坏。
我为此目的开发的代码在这里:
<?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 );
?>
序列化数据的示例:
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:"";}}
附加编辑
不幸的是，其他序列化数组中还有
/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/";...或者，
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. ";...因此，我认为简单的
preg_replace(或回调)不会解决问题，但是我想高级的可能吗？我的问题是:
我很害怕看到我的代码有问题(不好的程序员，我很抱歉)，并且对使用此代码运行测试有些担心。
最终编辑:工作代码
因为我的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);
}
?>
这段代码将我的新SQL文件放在同一目录(
X:\wamp\www)中，以便我进一步操作。我在数据重复方面遇到了一些问题，由于某种原因，文件中仍然有67个
/dev实例，但是我使用Notepad++和WinMerge进行了所有排序，最后花了大约45分钟的时间进行搜索/替换一个超过9000万个字符的数据库。 最佳答案
当我遇到同样的问题时，我运行了数据库的mysqldump，然后在使用SQL创建新数据库之前，在文本编辑器中打开并仅搜索/替换了值。非常简单，出奇的快速，特别是一口气。
如前所述，序列化数据存在问题，因此您可以使用简单的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);
}
?>
注意:可以在mysqldump上使用，如果要测试，则需要在
\\\中的"之前删除preg_replace_callback-这只是mysqldump的转义引号。另请注意:有两个preg替换(一个用于普通URL，一个用于服务器路径)，一个str替换剩余的标准URL。