絵文字🐼が入っているWordpressの記事をデータベースに抽出しようとしたら、テーブルの文字コードがutf8で大惨事になりかけた件(おまけ付き)。

はじめに

「サブタイトルネタ帳」のネタがそれなりにたまってきましたが、以下の理由により、ネタについてはWordpressのデータベースにテーブルを作成し、そこで管理することにしました。

ネタを追加するたびに、ネタが追加されたからというだけの理由で「サブタイトルネタ帳」のページのリビジョンが進んでしまうのもいかがなものかと思ったこと。
サブタイトルはタイトル「panda大学習帳」の下に表示しますが、それと「サブタイトルネタ帳」のページと「panda大学習帳外伝」のサブタイトルを自動的に同期させる手段がないために手動で同期せざるを得ず、かなりの手間がかかること。

…というわけでWordpressのデータベースにテーブルを作成後、サブタイトルネタ帳のページからネタのみをコピペでテキストファイルに書き出してそれをSQL文に変換したところで…

ふと思ったのが…

絵文字が入っているお（＾ω＾）

ということにSQL文を生成してから気が付いてしまいました。

自分でサブタイトルのネタを考えておきながらすっかり忘れてしまっていて、お恥ずかしい限りです。(´・ω・｀)

ということで、ちょっといやな予感がしてきたので、本サイトで稼働しているWordpressがデータ保存用に使っているMariaDBの文字コードを調べてみることにしました。

最初に作ったサブタイトルのネタ保存用テーブルの作成用SQL文

最初に作ったサブタイトルのネタ保存用テーブルの作成用SQL文は以下のようなもので、これをWordpress用のMariaDBで実行してテーブルを作成してしまいました。

create table catchphrase (
       id int(11) not null auto_increment,
       catchphrase varchar(4096) default null,
       created_at timestamp not null default current_timestamp,
       primary key(id)
) ENGINE=InnoDB default charset=utf8;

SQL文をご存知の方なら一撃でどこが問題なのかがおわかりになるかと思いますが、これでは絵文字が格納できません。orz

一応MariaDBに接続して、テーブルの文字コードを確認しますが…

MariaDB [pandanote]> select table_name,table_collation from information_schema.tables where table_schema=’pandanote’;
+—————————–+——————–+
| table_name                  | table_collation    |
+—————————–+——————–+
| amazon_access_key           | utf8_general_ci    |
| amazon_product_link         | utf8_general_ci    |
| catchphrase                 | utf8_general_ci    |
| wp__wsd_plugin_alerts       | utf8mb4_unicode_ci |
| wp__wsd_plugin_live_traffic | utf8mb4_unicode_ci |
| wp__wsd_plugin_scan         | utf8mb4_unicode_ci |
| wp__wsd_plugin_scans        | utf8mb4_unicode_ci |
| wp_commentmeta              | utf8mb4_unicode_ci |
| wp_comments                 | utf8mb4_unicode_ci |
| wp_ewwwio_images            | utf8mb4_unicode_ci |
| wp_links                    | utf8mb4_unicode_ci |
| wp_nxs_log                  | utf8mb4_unicode_ci |
| wp_nxs_query                | utf8mb4_unicode_ci |
| wp_options                  | utf8mb4_unicode_ci |
| wp_postmeta                 | utf8mb4_unicode_ci |
| wp_posts                    | utf8mb4_unicode_ci |
| wp_term_relationships       | utf8mb4_unicode_ci |
| wp_term_taxonomy            | utf8mb4_unicode_ci |
| wp_termmeta                 | utf8mb4_unicode_ci |
| wp_terms                    | utf8mb4_unicode_ci |
| wp_usermeta                 | utf8mb4_unicode_ci |
| wp_users                    | utf8mb4_unicode_ci |
| wp_yarpp_related_cache      | utf8mb4_unicode_ci |
+—————————–+——————–+
25 rows in set (0.012 sec)

catchphraseテーブルを作成したわけですが、文字コードはutf8になっています。

ダメなものはダメですね… (´・ω・｀)

そこで、作り直しです。

そこで、テーブル作成用のSQL文を以下のように書き直して再度実行し、テーブルを作り直します。

drop table if exists catchphrase;
create table catchphrase (
       id int(11) not null auto_increment,
       catchphrase varchar(4096) default null,
       created_at timestamp not null default current_timestamp,
       primary key(id)
) ENGINE=InnoDB default charset=utf8mb4;

再度MariaDBに接続して、文字コードを確認してみます…

MariaDB [pandanote]> select table_name,table_collation from information_schema.tables where table_schema=’pandanote’;
+—————————–+——————–+
| table_name                  | table_collation    |
+—————————–+——————–+
| amazon_access_key           | utf8_general_ci    |
| amazon_product_link         | utf8_general_ci    |
| catchphrase                 | utf8mb4_general_ci |
| wp__wsd_plugin_alerts       | utf8mb4_unicode_ci |
| wp__wsd_plugin_live_traffic | utf8mb4_unicode_ci |
| wp__wsd_plugin_scan         | utf8mb4_unicode_ci |
| wp__wsd_plugin_scans        | utf8mb4_unicode_ci |
| wp_commentmeta              | utf8mb4_unicode_ci |
| wp_comments                 | utf8mb4_unicode_ci |
| wp_ewwwio_images            | utf8mb4_unicode_ci |
| wp_links                    | utf8mb4_unicode_ci |
| wp_nxs_log                  | utf8mb4_unicode_ci |
| wp_nxs_query                | utf8mb4_unicode_ci |
| wp_options                  | utf8mb4_unicode_ci |
| wp_postmeta                 | utf8mb4_unicode_ci |
| wp_posts                    | utf8mb4_unicode_ci |
| wp_term_relationships       | utf8mb4_unicode_ci |
| wp_term_taxonomy            | utf8mb4_unicode_ci |
| wp_termmeta                 | utf8mb4_unicode_ci |
| wp_terms                    | utf8mb4_unicode_ci |
| wp_usermeta                 | utf8mb4_unicode_ci |
| wp_users                    | utf8mb4_unicode_ci |
| wp_yarpp_related_cache      | utf8mb4_unicode_ci |
+—————————–+——————–+
25 rows in set (0.007 sec)

文字コードが変更されていることが確認できました。(｀・ω・´)

データを流し込んでみます。

次に、以下のようなSQL文(長いので、途中を省略しています。)を実行してみます。なお、このSQL文は簡単な変換プログラムをPython3で自作して、それを使用して生成しました。

insert into catchphrase(catchphrase) value(‘ここ数年で一番のデスマーチ。’);
insert into catchphrase(catchphrase) value(‘京阪乗る人、おけいきゅう。’);
insert into catchphrase(catchphrase) value(‘夢と希望と、センターと国立大学と。’);
(中略)
insert into catchphrase(catchphrase) value(‘壮大なスケールでお送りする前フリ。’);
insert into catchphrase(catchphrase) value(‘全戸、前向き。’);
insert into catchphrase(catchphrase) value(‘ご注文は、繰り上げ返済ですか?’);

データの流し込みは以下の方法で実行するのですが…

[panda@pnr customize]$ mysql -u panda -ppanda pandanote < catchphrase_migration_20181116.sql
ERROR 1366 (22007) at line 25: Incorrect string value: ‘\xF0\x9F\x98\xBA\xE3\x80…’ for column ‘catchphrase’ at row 1

なんか、ダメみたいですね… (´・ω・｀)

そこで、前節のデータ流し込み用のSQL文の先頭に

SET NAMES utf8mb4

を追加してテーブルを作り直し、再度データ流し込み用のSQL文を実行してみたところ、無事すべてのSQL文が実行されました。(｀・ω・´)

WordPressのショートコードをサクサクと書きます。

これでWordpressのショートコードを書く準備が整いましたので、サクサクと書きます。

例によってfunctions.phpに以下のコードを追加します。

	function dump_catchphrase() {
	global $wpdb;
	$cc = '<ol>';
	wpdb->get_results("select catchphrase from catchphrase",ARRAY_A);
	wpdb->num_rows;
	foreach (row) {
	row['catchphrase'].'</li>';
	}
	return $cc . '</ol>';
	}

	add_shortcode('dump-catchphrase','dump_catchphrase');