研究室の一般ブログと、教員のブログとを、multisite を使ってそれぞれ個別の wordpress で管理していた。ところが、どうにもこれが polylang と相性が悪い。
サーバの更新に合わせて、これを統合して一つの wordpress のもとでカテゴリーに分けて管理することにした。ポストを移行できればよしとして、そのカテゴリー・言語などは、受け側で改めて設定することにする。以下、その手順を示す。
データベースは MySQL を使用している。統合される側(現在実行されている側)のプリフィックスを new_
、ポストだけ取り出して流し込む側のプリフィックスを old_
とする。
流し込みデータの準備
SQL Dump の中から、CREATE TABLE old_posts
行を探してくると、こんな感じ(,
を ,改行
に置換済み):
CREATE TABLE `old_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_title` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_excerpt` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`to_ping` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`pinged` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT 0,
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`),
KEY `post_name` (`post_name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=3553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
- CREATE TABLE `old_posts` (
- `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
- `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
- `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
- `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
- `post_title` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
- `post_excerpt` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
- `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
- `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
- `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
- `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
- `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
- `to_ping` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
- `pinged` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
- `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
- `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
- `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
- `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
- `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
- `menu_order` int(11) NOT NULL DEFAULT 0,
- `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
- `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
- `comment_count` bigint(20) NOT NULL DEFAULT 0,
- PRIMARY KEY (`ID`),
- KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
- KEY `post_parent` (`post_parent`),
- KEY `post_author` (`post_author`),
- KEY `post_name` (`post_name`(191))
- ) ENGINE=InnoDB AUTO_INCREMENT=3553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `old_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_title` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_excerpt` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`to_ping` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`pinged` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT 0,
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`),
KEY `post_name` (`post_name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=3553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
このデータ構造が new_posts
と一致しているのを確認する。
流し込み作業
このうち、自動連番の ID
フィールドを除くフィールドを新規レコードで新しいテーブルに流し込んでやればよいので、INSERT INTO SELECT
命令を使って、一気に処理する。
insert into new_posts (
post_author,
post_date,
post_date_gmt,
post_content,
post_title,
post_excerpt,
post_status,
comment_status,
ping_status,
post_password,
post_name,
to_ping,
pinged,
post_modified,
post_modified_gmt,
post_content_filtered,
post_parent,
guid,
menu_order,
post_type,
post_mime_type,
comment_count
)
select
post_author,
post_date,
post_date_gmt,
post_content,
post_title,
post_excerpt,
post_status,
comment_status,
ping_status,
post_password,
post_name,
to_ping,
pinged,
post_modified,
post_modified_gmt,
post_content_filtered,
post_parent,
guid,
menu_order,
post_type,
post_mime_type,
comment_count
from old_posts;
- insert into new_posts (
- post_author,
- post_date,
- post_date_gmt,
- post_content,
- post_title,
- post_excerpt,
- post_status,
- comment_status,
- ping_status,
- post_password,
- post_name,
- to_ping,
- pinged,
- post_modified,
- post_modified_gmt,
- post_content_filtered,
- post_parent,
- guid,
- menu_order,
- post_type,
- post_mime_type,
- comment_count
- )
- select
- post_author,
- post_date,
- post_date_gmt,
- post_content,
- post_title,
- post_excerpt,
- post_status,
- comment_status,
- ping_status,
- post_password,
- post_name,
- to_ping,
- pinged,
- post_modified,
- post_modified_gmt,
- post_content_filtered,
- post_parent,
- guid,
- menu_order,
- post_type,
- post_mime_type,
- comment_count
- from old_posts;
insert into new_posts (
post_author,
post_date,
post_date_gmt,
post_content,
post_title,
post_excerpt,
post_status,
comment_status,
ping_status,
post_password,
post_name,
to_ping,
pinged,
post_modified,
post_modified_gmt,
post_content_filtered,
post_parent,
guid,
menu_order,
post_type,
post_mime_type,
comment_count
)
select
post_author,
post_date,
post_date_gmt,
post_content,
post_title,
post_excerpt,
post_status,
comment_status,
ping_status,
post_password,
post_name,
to_ping,
pinged,
post_modified,
post_modified_gmt,
post_content_filtered,
post_parent,
guid,
menu_order,
post_type,
post_mime_type,
comment_count
from old_posts;