ブログ記事タイトルの一括変換

経緯

 Google Search Consoleで、ブログ記事のhtmlタイトルが「重複」していると指摘されていたので、改善しました。重複していたのはカテゴリ「釣り」の記事で、慣例上タイトルは「(釣りスタイル)_(釣りもの)」というフォーマットにしています。よって例えばボート釣りでアジを釣った時の記事はみんな同じタイトルになります。Movable Typeはこの記事タイトルにブログ名称を付加したものをhtmlタイトルとして出力しています。つまり全く同じhtmlタイトルの記事が増産されるわけです。  これではSEO上まずそうです。クライアント(CSEやA5:SQL MK2などのGUIを良く使っています)上で1個づつタイトルを変更しても良いのですが、この時点で70個近い記事をこなすのは大変なので、ここで紹介する簡単なプログラムを作って一括変換しました。

DB上のMovable Typeのエントリータイトル

この記事で既に確認した設計ルールに加えて下記のルールをER図により確認

  • エントリータイトルは、mt_entryテーブルにのみ存在する
  • そのタイトルフィールドはentry_title

前回のルールを踏まえると、mtplacementテーブルとmtentryを単純結合すれば「釣り」カテゴリーのエントリー記事レコードを抽出できそうです。

「釣り」カテゴリーのエントリー記事の抽出

下記のSQLにより抽出しました。
mtplacement.placementcategory_id = 28
の28が釣りカテゴリーのカテゴリーIDです。(管理画面で確認しました)

select mt_entry.entry_id,mt_entry.entry_created_on,mt_entry.entry_title from mt_entry,mt_placement
 where mt_entry.entry_id = mt_placement.placement_entry_id
 and mt_entry.entry_blog_id = mt_placement.placement_blog_id
 and mt_placement.placement_category_id = 28
 order by mt_entry.entry_id

コードの紹介

下記はCGIを作って一気に変換したときのコードです。上記SQLで抽出したレコード毎のエントリー生成日をタイトルに付加してupdateします。DBはutf8前提で、コードもutf8保存前提です。一時的なものなので今はサーバーから削除しました。
DBIを使用しています。「$dbh->do("set names utf8");」を忘れると文字化けします。

#!/usr/bin/perl -w
use DBI;
print "Content-type: text/html\n\n";
print "<html>\n";
my $dsn = "DBI:mysql:database=***_mt;host=127.0.0.1;port=3306";
my $dbh = DBI->connect($dsn, '******', '******');
$dbh->do("set names utf8");
my $sth = $dbh->prepare("「上記SQL文」");
$sth->execute();
my @dda = ();
my $i=0;
while (my $ref = $sth->fetchrow_hashref()) {
 print "entry_id = $ref->{'entry_id'}, 生成日 = $ref->{'entry_created_on'},
 title = $ref->{'entry_title'}<br/>\n";
 $dda[$i]->{entry_id} = $ref->{'entry_id'};
 $dda[$i]->{entry_created_on} = $ref->{'entry_created_on'};
 $dda[$i]->{entry_title} = $ref->{'entry_title'};
 $i++;
}
$sth->finish();
my $rv;
for(@dda){
 my $crda = [split(/\s/,$_->{entry_created_on})]->[0];
 $rv = $dbh->do("update mt_entry set entry_title = '$_->{entry_title} $crda'
 where entry_id = $_->{entry_id}");
}
$dbh->disconnect();
print "</html>\n";

一括変換後は当然再構築して静的ファイルを再作成しました。

このままでは釣りカテゴリーの記事を書くたび生成日をタイトルに手動で付加しなければならないので、記事投稿や更新時に自動で付加してくれる簡単なプラグインを作ってみました。下記で公開しています。Readmeもありますので興味のある方は自由にどうぞ。紹介記事はまた後日。

[GitHub]