カテゴリー: SQL

Webサイトの作成に欠かせない、データベースにアクセスするための言語です。

WordPress内の特定文字列をMySQLのREPLACEで一括置換する方法

WordPressで、例えば移転したブログのURLなど、あっちこっちのページに散在している特定の文字列をすべて、いっぺんに書き替えたい（一括置換したい）という場合の方法です。MySQLのREPLACEコマンドを使います。

WordPressで、例えば移転したブログのURLなど、あっちこっちのページに散在している特定の文字列をすべて、いっぺんに書き替えたい（一括置換したい）という場合の方法です。

MySQLのREPLACEコマンドを使います。

REPLACE(対象となる文字列, '変更前の文字列', '変更後の文字列');

参考URL:MySQL :: MySQL 5.7 Reference Manual :: 13.2.8 REPLACE Syntax

記事本文内の置換

ページ本文の中の次のような状態のHTMLで、example.comを、newurl.net に変更したいとします。

URLは<a href="http://example.com/">http://example.com/</a>です。

<a>タグのhrefも含めてすべて一括置換するには

UPDATE wp_posts SET post_content = REPLACE(post_content, 'example\.com', 'newurl\.net');

とします。これだけで、すべてのページのURLを一瞬ですべて変更出来ます。注意点は３つです。

ドット . の前には\をつける。
この時、 . ドットは \ でエスケープしますが、わからなければそのまま、example.comでも大抵は問題ありません。(バックスラッシュの \ はキーボードひらがなの「ろ」にありますが、「Back Space」キーの左の円通貨記号を押しても同じです。というか、文字コードが同じなのでどちらを押しても円記号になってしまう)

この時の . ドットは、バックスラッシュ \ がない場合は何か一文字という意味で、comの前やnetの前の何か一文字を意味します。なので example.com ではなく example1comやexampledcomなどという文字列が偶然あれば、それもnewurl.netに変換してしまいます。

置換前文字列と置換後文字列は ‘ シングルクォートで囲む。
シングルクォート ‘ ではなく、ダブルクォート ” でもいいのですが、HTMLのタグの中にダブルクォートがたくさんあるので、シングルクォートの方が混乱しないかと思い、シングルクォートでと書いています。

上記の例の場合囲むのは ‘example\.com’ と ‘newurl\.net’ の２つです。

テーブル接頭辞の確認

phpMyAdmin

また、wp_postsはテーブルの名前ですが、「データベースが1つだけのサーバーでWordPressを複数設置する方法」のような理由で、テーブル接頭辞をつけている場合には、それに合わせます。どんな接頭辞をつけたかは、phpMyAdminの左側を探せばわかります。

phpMyAdminを使う場合、具体的には、下の図の赤枠にある「SQL」をクリックして、開いたページの入力欄に打ち込むか貼り付けます。

phpMyAdmin

入力欄の右下にある「実行」ボタンを押して、問題がなければ「何行変更しました」と表示されて完了です。

phpMyAdmin

コメント欄の文字列一括置換

コメント本文の特定文字列を置換するには次のようにします。

UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'hoge\.com', 'fuga\.net');

Before
コメント置換

After
コメント置換

また、コメント本文ではなく、コメント送信者のURLを変更するには次のようにします。コメント本文では対象カラムがcomment_contentでしたが、送信者URLの場合は comment_author_url になります。

UPDATE wp_comments SET comment_author_url = REPLACE(comment_author_url, 'hoge\.com', 'fuga\.net');

通常、投稿者URLは個別ページのURLではなく、トップページのURLで固定です。その場合、次のやり方の方が正統派ですが、この例ではどちらでも同じ結果になります。SQLを知らない場合は、上の例でやってください。

UPDATE wp_comments SET comment_author_url = 'http://fuga.net/' WHERE comment_author_url = 'http://hoge.com/';

置換（置き換え）ではなく、特定文字列を削除したい

hoge.comをすべて削除するなら、最後の「置換後文字列」のシングルクォート（ダブルクォートでもよい）の中に何も入れず空にします。

コメント中のhoge.comをすべて削除する場合

UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'hoge\.com', '');

MySQLのストアドプロシージャでIF文のインデントはNGぽい

「ハロウィンの運命」というアプリを作ってみたんだけど
MySQLのストアドプロシージャ記述でハマりました。

どこが間違っているのかどうしてもわからずにいろいろやってみたんだけど、
ずっとエラーが解消されない。大雑把に書くとかんなかんじ。

ちなみに、ひとつ目のSELECTとふたつ目のSELECTは対象のテーブルが違います。
最初のSELECTでパラメーターの確認をして、正しければ本来の処理を行なうという内容です。

delimiter //
CREATE PROCEDURE fortune(OUT _s VARCHAR(2048), IN _u VARCHAR(128))
BEGIN
IF (SELECT COUNT(*) FROM fortune_type WHERE name=_u) = 0 THEN
	SELECT '"type" パラメーターが間違っています' INTO _s;
ELSE
	IF (SELECT COUNT(*) FROM fortune_name WHERE name=_u) = 1 THEN
		DELETE FROM fortune_saved WHERE name=_u;
	END IF;
(中略)
END IF;
END;
//
delimiter ;

これを、ブロックごとに分けて登録してみるとすんなりOKになる。
どうも文法的には間違っていないっぽい。

IF文のネストがあったので、そこを字下げしてたんだけど
試しにと思って、インデントを削除してみたらすんなり入った。

IFの前にはインデントをつけたらいけないみたいです。

これだとOKになる。

delimiter //
CREATE PROCEDURE fortune(OUT _s VARCHAR(2048), IN _u VARCHAR(128))
BEGIN
IF (SELECT COUNT(*) FROM fortune_type WHERE name=_u) = 0 THEN
	SELECT '"type" パラメーターが間違っています' INTO _s;
ELSE
IF (SELECT COUNT(*) FROM fortune_name WHERE name=_u) = 1 THEN
	DELETE FROM fortune_saved WHERE name=_u;
END IF;
(中略)
END IF;
END;
//
delimiter ;