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

By | 2018年11月17日
0 Comment

はじめに

「サブタイトルネタ帳」のネタがそれなりにたまってきましたが、以下の理由により、ネタについては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に以下のコードを追加します。

なお、実際に本Webサイトで稼働しているコードは、上記のコードに広告表示用のコードを追加しています。

動作確認。

変更前と同様の表示ができることを確認してみます。

表示できているようです。

まとめ

「Wordpress上で表示ができているのだから、PHPも問題なさそうだし、Python3でのSQL文の作成もうまくいっているので、作ったSQL文をMariaDBにinsertすれば作業完了じゃん。?」

と、そんな風に考えていた時期がありましたが、データをデータベースに流し込む直前に気が付いて良かったです。

引き続き、以下の処理を行うプログラムかスクリプトを書くことになりますが、別記事を立てて書く話にできるようなら書きます。

  1. WordPressのwp_optionsテーブルからblog_descriptionの設定(?)をcatchphraseテーブルにコピーする。
  2. 上記の処理のついでにbrタグを削除する。

特にMySQLやMariaDBを扱うときには、utf8にせねばならない強い理由がない限り、データベース(またはテーブル)の文字コードをutf8mb4にしておいた方が無難かもしれませんね。

この記事は以上です… といいたいところですが、参考文献の後ろにおまけがありますので、ついでにご覧いただけると幸いです。

References / 参考文献

おまけ: Fedora 29においてブラウザを使わずに絵文字を確認する方法。

SQL文に絵文字が含まれている場合にはEmacsでそのSQL文を開くと、確認することができるようです。

Related Posts / 関連ページ

WordPressのキャッチフレーズをheader.phpに直接書き込まないで2行以上表示する方法を試してみたところ、もう1ヵ所修正が必要だった件(おまけ付き) Let’s encryptのドメイン認証の方法をHTTP-01に変更するための準備で試行錯誤した件。 httpからhttpsへのリダイレクトを削減するためのnginxの設定。 メモリ不足気味の本Webサーバをスケールアップしたついでに、rootファイルシステムのパーティションのサイズも変えてみた。