WordPressでデータベース上のURL（ドメイン）を一気に書き換える

日がたって、改めてこのコードを読んでみたら、とってもリファクタリングの余地が大きいので、そのつもりで使ってください。いちおう動くことは動くと思いますが。

＊　＊　＊

WordPress のDB上のサイトURLを一気に変換 を参考に、マルチサイト化したWordPressでも、データベース内のドメインを一気に変更するようにしました。

WordPressの引っ越しの際など、コンテンツ内のドメインも一気に書き換えられるので、便利です。

をかもと氏（上述のサイトの方）が、httpサイトとhttpsサイトのことも意識しておられたので、そのあたりの変換も大丈夫でした。

使い方

以下のコードをコピーして、WordPressをインストールしたフォルダ、wp-config.phpと同じ階層にwp_replace-siteurl.phpという名前で保存します。文字コードはASCIIにしてください。

http://{www.example.com}/wp_replace-siteurl.php?&old={old_domain}&passphrase={passphrase}

というような案配で、phpをたたいてください。passphraseはただの気休めですが、適宜変更してください。

なお、いちおう可逆の操作ではありますが、くれぐれもデータベースのバックアップを忘れないように。また、すんだら、このファイルをきちんと削除するようにしてください。

あと、ときどきwp_optionsあたりに物理パスが入っていることがありますが、まあ、これは適宜修正してください。

コード

<?php
/*
thx:
http://dogmap.jp/2012/09/20/wordpress-replace-siteurl/

ussage:
You MUST backup your database
http://{www.example.com}/wp_replace-siteurl.php?&old={old_domain}&passphrase={passphrase}

sample:
http://www.example.com/wp_replace-siteurl.php?old=www.example.com&new=https://www1.example.com&passphrase=A9ykzk3x
*/

//passphrase
//this is only placebo

$passphrase = 'A9ykzk3x' ;//edit here

if( ( @$_GET['passphrase'] != $passphrase ) ){
	echo "add passphrase.";
	exit();
}

//newsite

if( @$_GET['new'] ){
	$new_url = $_GET['new'];
	$old_url = isset( $_GET['old'] ) ? $_GET['old'] : '' ;
}else{
	echo "please input new site url and wp directory name!\n";
	exit();
}

$path = dirname( __FILE__ ) . '/' ;

if ( !file_exists($path.'wp-load.php') && file_exists($path.'wp-config.php') ) {
	echo "Not found wp-config.php!\n";
	exit();
}
require_once(file_exists($path.'wp-load.php') ? $path.'wp-load.php' : $path.'wp-config.php');

global $wpdb;

//multisite
$is_blogs_exist = FALSE ;
if( $wpdb->blogs ){
	$sql = "SHOW TABLES FROM ".DB_NAME." LIKE '{$wpdb->blogs}';" ;
	$is_blogs_exist = $wpdb->get_results($sql);
}

$blog_ids = array();
if( $is_blogs_exist ){
	$blogs= $wpdb->get_results("
		SELECT blog_id
		FROM {$wpdb->blogs}
		WHERE site_id= '{$wpdb->siteid}'
		AND spam= '0'
		AND deleted= '0'
		AND archived= '0'
	");
	foreach($blogs as $val){
		$blog_ids[]= $val->blog_id;
	}
}else{
	$blog_ids = array(1);
}

echo "<h1>WordPress Replace Siteurl</h1>";

//wp_blogs
echo "<h2>common</h2>";
$replace = new ReplaceSiteURL($new_url , $old_url , 1);
if( count( $blog_ids ) > 2 ){
	echo "{$replace->prefix}blogs : replace '{$replace->old_url_noschema}' to '{$replace->new_url_noschema}' ...<br />\n";
	printf("Result: %d\n\n", $replace->blogs());
	echo "<br />\n" ;
}

//wp_site
echo "{$replace->prefix}site : replace '{$replace->old_url_noschema}' to '{$replace->new_url_noschema}' ...<br />\n";
printf("Result: %d\n\n", $replace->site());
echo "<br />\n" ;

//wp_sitemeta
echo "{$replace->prefix}sitemeta : replace '{$replace->old_url_noschema}' to '{$replace->new_url_noschema}' ...<br />\n";
printf("Result: %d\n\n", $replace->sitemeta());
echo "<br />\n" ;

//each
foreach( $blog_ids as $blog_id ){
	$replace = new ReplaceSiteURL($new_url , $old_url , $blog_id);

	echo "<h2>blog_id {$blog_id}</h2>";

	// wp_options
	echo "{$replace->prefix}options : replace '{$replace->old_url}' to '{$replace->new_url}' ...<br />\n";
	printf("Result: %d\n\n", $replace->options($blog_id));
	echo "<br />\n" ;

	// wp_posts
	echo "{$replace->prefix}posts : replace '{$replace->old_url}' to '{$replace->new_url}' ...<br />\n";
	printf("Result: %d\n\n", $replace->posts($blog_id));
	echo "<br />\n" ;

	// wp_posts - permalink
	echo "{$replace->prefix}posts : replace permalink '{$replace->old_url}' to '{$replace->new_url}' ...<br />\n";
	printf("Result: %d\n\n", $replace->posts_permalink($blog_id));
	echo "<br />\n" ;

	// wp_postmeta
	echo "{$replace->prefix}postmeta : replace '{$replace->old_url}' to '{$replace->new_url}' ...<br />\n";
	printf("Result: %d\n\n", $replace->postmeta($blog_id));
	echo "<br />\n" ;

	// wp_commentmeta
	echo "{$replace->prefix}commentmeta : replace '{$replace->old_url}' to '{$replace->new_url}' ...<br />\n";
	printf("Result: %d\n\n", $replace->commentmeta($blog_id));
	echo "<br />\n" ;
	
	if( $blog_id == 1 ){
		// wp_usermeta
		echo "{$replace->prefix}usermeta : replace '{$replace->old_url}' to '{$replace->new_url}' and '{$replace->old_url_noschema}' to '{$replace->new_url_noschema}' ...<br />\n";
		printf("Result: %d\n\n", $replace->usermeta($blog_id));
		echo "<br />\n" ;
	}
}

echo "<p>done.</p><p><strong>please remove wp-replace-siteurl.php</strong> from your server.</p>";
exit();

// replace class
class ReplaceSiteURL {
	public $new_url;
	public $old_url;
	public $new_url_noschema;
	public $old_url_noschema;
	public $prefix;

	function __construct($new_url , $old_url , $blog_id) {
		global $wpdb;

		//url
		$this->new_url = untrailingslashit(preg_match('/^https?:\/\//i', $new_url) ? $new_url : 'http://'.$new_url );
		$old_url = (!empty($old_url) && !preg_match('/^https?:\/\//i', $old_url) ? 'http://'.$old_url : $old_url );
		$this->old_url = untrailingslashit(empty($old_url) ? home_url() : $old_url);

		$this->old_url_noschema = str_replace( array( 'http://','https://' ) , '' , $this->old_url ) ;
		$this->new_url_noschema = str_replace( array( 'http://','https://' ) , '' , $this->new_url ) ;

		//prefix
		if( $blog_id != 1 ){
			$this->prefix = $wpdb->base_prefix . $blog_id . '_' ;
		}else{
			$this->prefix = $wpdb->base_prefix ;
		}
	}

	// wp_site
	public function site() {
		global $wpdb;

		$sql = $wpdb->prepare(
			"UPDATE `{$this->prefix}site` SET domain=REPLACE(domain, \"%s\",\"%s\") where domain like \"%s\"",
			$this->old_url_noschema,
			$this->new_url_noschema,
			"%{$old_url}%"
		);

		return $wpdb->query($sql);
	}

	// wp_sitemeta
	public function sitemeta() {
		global $wpdb;

		$sql = $wpdb->prepare(
			"UPDATE `{$this->prefix}sitemeta` SET meta_value=REPLACE(meta_value, \"%s\",\"%s\") where meta_value like \"%s\"",
			$this->old_url_noschema,
			$this->new_url_noschema,
			"%{$old_url}%"
		);

		return $wpdb->query($sql);
	}

	// wp_blogs
	public function blogs() {
		global $wpdb;

		$old_url = str_replace( array( 'http://','https://' ) , '' , $this->old_url ) ;
		$new_url = str_replace( array( 'http://','https://' ) , '' , $this->new_url ) ;

		$sql = $wpdb->prepare(
			"UPDATE `{$this->prefix}blogs` SET domain=REPLACE(domain, \"%s\",\"%s\") where domain like \"%s\"",
			$old_url,
			$new_url,
			"%{$old_url}%"
		);

		return $wpdb->query($sql);
	}

	// wp_options
	public function options() {
		global $wpdb;

		$count = 0;
		$sql = $wpdb->prepare(
			"SELECT * from `{$this->prefix}options` where option_value like \"%s\"",
			'%'.untrailingslashit($this->old_url).'%'
			);
		$results = $wpdb->get_results($sql);
		foreach ($results as $result){
			$sql = $wpdb->prepare(
				"UPDATE `{$this->prefix}options` SET option_value=\"%s\" where option_id = %d",
				$this->replace($this->old_url, $this->new_url, $result->option_value) ,
				$result->option_id
				);
			$wpdb->query($sql);
			$count++;
		}

		return $count;
	}

	// wp_posts
	public function posts() {
		global $wpdb;

		$sql = $wpdb->prepare(
			"UPDATE `{$this->prefix}posts` SET post_content=REPLACE(post_content, \"%s\",\"%s\") where post_content like \"%s\"",
			$this->old_url,
			$this->new_url,
			"%{$this->old_url}%"
		);

		return $wpdb->query($sql);
	}
	
	// posts_permalink
	public function posts_permalink() {
		global $wpdb;

		$sql = $wpdb->prepare(
			"UPDATE `{$this->prefix}posts` SET guid=REPLACE(guid, \"%s\",\"%s\") where guid like \"%s\"",
			$this->old_url,
			$this->new_url,
			"%{$this->old_url}%"
		);

		return $wpdb->query($sql);
	}
	
	// wp_postmeta
	public function postmeta() {
		global $wpdb;
	
		$count = 0;
		$sql = $wpdb->prepare(
			"SELECT * from `{$this->prefix}postmeta` where meta_value like \"%s\"",
			'%'.untrailingslashit($this->old_url).'%'
			);
		$results = $wpdb->get_results($sql);
		foreach ($results as $result){
			$sql = $wpdb->prepare(
				"UPDATE `{$this->prefix}postmeta` SET meta_value=\"%s\" where meta_id = %d",
				$this->replace($this->old_url, $this->new_url, $result->meta_value) ,
				$result->meta_id
				);
			$wpdb->query($sql);
			$count++;
		}

		return $count;
	}
	
	// wp_usermeta
	public function usermeta() {
		global $wpdb;

		$count = 0;
		$sql = $wpdb->prepare(
			"SELECT * from `{$this->prefix}usermeta` where meta_value like \"%s\"",
			'%'.untrailingslashit($this->old_url).'%'
			);
		$results = $wpdb->get_results($sql);
		foreach ($results as $result){
			$sql = $wpdb->prepare(
				"UPDATE `{$this->prefix}usermeta` SET meta_value=\"%s\" where umeta_id = %d",
				$this->replace($this->old_url, $this->new_url, $result->meta_value) ,
				$result->umeta_id
				);
			$wpdb->query($sql);
			$count++;
		}

		$sql = $wpdb->prepare(
			"SELECT * from `{$this->prefix}usermeta` where meta_value like \"%s\"",
			'%'.untrailingslashit($this->old_url_noschema).'%'
			);
		$results = $wpdb->get_results($sql);
		foreach ($results as $result){
			$sql = $wpdb->prepare(
				"UPDATE `{$this->prefix}usermeta` SET meta_value=\"%s\" where umeta_id = %d",
				$this->replace($this->old_url_noschema, $this->new_url_noschema, $result->meta_value) ,
				$result->umeta_id
				);
			$wpdb->query($sql);
			$count++;
		}

		return $count;
	}
	
	// wp_commentmeta
	public function commentmeta() {
		global $wpdb;
	
		$count = 0;
		$sql = $wpdb->prepare(
			"SELECT * from `{$this->prefix}commentmeta` where meta_value like \"%s\"",
			'%'.untrailingslashit($this->old_url).'%'
			);
		$results = $wpdb->get_results($sql);
		foreach ($results as $result){
			$sql = $wpdb->prepare(
				"UPDATE `{$this->prefix}commentmeta` SET meta_value=\"%s\" where meta_id = %d",
				$this->replace($this->old_url, $this->new_url, $result->meta_value) ,
				$result->meta_id
				);
			$wpdb->query($sql);
			$count++;
		}

		return $count;
	}
	
	private function replace($origin, $replaced, $value) {
		if ( is_serialized($value) ) {
			$value = maybe_unserialize($value);
			$value = $this->deep_replace($origin, $replaced, $value);
			$value = maybe_serialize($value);
		} else {
			$value = str_replace($origin, $replaced, $value);
		}
		return $value;
	}
	
	private function deep_replace($origin, $replaced, $datas) {
		if ( is_array($datas) || is_object($datas) ) {
			foreach ( $datas as &$data ) {
				if ( is_array($data) || is_object($data) ) {
					$data = $this->deep_replace($origin, $replaced, $data);
				} else {
					$data = str_replace($origin, $replaced, $data);
				}
			}
		}
		return $datas;
	}
}