wordpressのマルチサイトで各サイトの記事を混ぜて表示する方法を書きましたが、途中SQL文の「UNION ALL」の部分でエラーが出てハマっていたのでメモ。

wordpressのマルチサイトで各サイト(ブログ)の記事を混ぜて一覧表示する

  1. wordpressのマルチサイトで各サイトの記事を混ぜて表示する方法を考える
  2. MySQL – サイト毎に分かれている投稿テーブルを1つにまとめる
  3. MySQL – ブログIDを追加する
  4. MySQL – 1つにまとめたデータを投稿日順に並び替える
  5. MySQL – 1つにまとめたデータに取得件数を指定する
  6. 完成版(ページングなし) – wordpressのマルチサイトで各サイトの記事を混ぜて一覧表示する

スポンサーリンク

はじめに

wordpressのマルチサイトで各サイトの記事を混ぜて表示する方法の中で、「サイト毎に分かれている投稿テーブルを1つにまとめる」ところで、MySQLの「UNION ALL」を使用してまとめましたが、「Illegal mix of collations for operation 'UNION' 」のエラーがずっと出ていて、ハマってしまったのでその時の対処方法です。

今回やりたいこと

MySQLの「UNION ALL」使用時にでた「Illegal mix of collations for operation 'UNION' 」エラーの解決

制作環境 / テスト環境

  • wordpress サブディレクトリ型のマルチサイト

解決した方法

エラー内容と原因

↓上記記事内で使用しているコード(余計な部分は少し省きました)

SELECT *
	FROM
	(
		SELECT
			wp_posts.ID
		FROM wp_posts
		WHERE post_status = 'publish'
		AND post_type = 'news'

		UNION ALL

		SELECT
			wp_2_posts.ID
		FROM wp_2_posts
		WHERE post_status = 'publish'
		AND post_type = 'post'
	) u

こののSELECT文で取得しているのは、IDですが、他にもタイトルや本文なども取得できるので、試しにタイトル「post_title」に変更して取得してみたいと思います。

コードは、以下です。

SELECT *
	FROM
	(
		SELECT
			wp_posts.post_title
		FROM wp_posts
		WHERE post_status = 'publish'
		AND post_type = 'news'

		UNION ALL

		SELECT
			wp_2_posts.post_title
		FROM wp_2_posts
		WHERE post_status = 'publish'
		AND post_type = 'post'
	) u

そうすると、「ID」の時は問題ありませんでしたが、「post_title」にすると下記のようなエラーが出るようになってしまいました。。

Illegal mix of collations for operation 'UNION'

このエラーで調べてみると、どうやら文字コード?照合順序が違うっぽい、、

ということで、DBを確認してみることに・・・

  • 「wp_posts」・・・「utf8mb4_unicode_ci」
  • 「wp_2_posts」・・・「utf8mb4_unicode_520_ci」

となっていました。

実際は、他にも子サイトがあるのですが、親サイトの「posts」は、「utf8mb4_unicode_ci」。子サイトの「posts」は「utf8mb4_unicode_520_ci」となっているようです。

このエラーが出たwordpressは、Xserverの自動インストールで設置。
サブディレクトリ型のマルチサイト。
あとは、wordpressのネットワーク管理画面よりサイトの追加で子サイトを追加しています。

あまり詳しくないので詳細は分かりませんが、とりあえずエラー回避のためにはどれかに照合順序を合わせないといけなさそうです。
また、特定サイトのみ異なるのではなく、親サイトと子サイトで異なっているようなので、今回は、子サイトの方を親サイトの方に合わせることにしました。

  • 「wp_posts」・・・「utf8mb4_unicode_ci」
  • 「wp_2_posts」・・・「utf8mb4_unicode_520_ci」→「utf8mb4_unicode_ci」に!

解決したコード

SELECT *
	FROM
	(
		SELECT
			wp_posts.post_title
		FROM wp_posts
		WHERE post_status = 'publish'
		AND post_type = 'news'

		UNION ALL

		SELECT
			wp_2_posts.post_title COLLATE utf8mb4_unicode_ci
		FROM wp_2_posts
		WHERE post_status = 'publish'
		AND post_type = 'post'
	) u

このように、照合順序を合わせる側の取得フィールド名の後に、
「COLLATE」+「照合順序
をつなげて記述すればOKです。

エラーなく抽出できました!

ちなみに、他に本文「post_content」などもあった場合は、下記のようになります。

SELECT *
	FROM
	(
		SELECT
			wp_posts.ID,
			wp_posts.post_author,
			wp_posts.post_date,
			wp_posts.post_content,
			wp_posts.post_title,
			wp_posts.post_status,
			wp_posts.post_name,
			wp_posts.post_modified,
			wp_posts.post_type,
			wp_posts.guid
		FROM wp_posts
		WHERE post_status = 'publish'
		AND post_type = 'news'

		UNION ALL

		SELECT
			wp_4_posts.ID,
			wp_4_posts.post_author,
			wp_4_posts.post_date,
			wp_4_posts.post_content COLLATE utf8mb4_unicode_ci,
			wp_4_posts.post_title COLLATE utf8mb4_unicode_ci,
			wp_4_posts.post_status COLLATE utf8mb4_unicode_ci,
			wp_4_posts.post_name COLLATE utf8mb4_unicode_ci,
			wp_4_posts.post_modified,
			wp_4_posts.post_type COLLATE utf8mb4_unicode_ci,
			wp_4_posts.guid COLLATE utf8mb4_unicode_ci
		FROM wp_4_posts
		WHERE post_status = 'publish'
		AND post_type = 'post'
	) u

フィールド名ごとに「COLLATE〜」でつなげたら大丈夫でした。

フィールドが、文字列で入っている場合に、「COLLATE〜」で照合順序を合わせないとエラーが出るようでした。

IDなど整数で入っている箇所は、つけなくてもエラーは出ませんでした。

 

まとめ

照合順序が合ってないのでエラーが出ているところと「COLLATE〜」で対処すれば大丈夫そう、、というところまでは、すぐわかったのですが、実際どう記述すればいいのかというところで、なかなかうまくいかずで、ハマってしまいました。。

wordpressのマルチサイトで各サイト(ブログ)の記事を混ぜて一覧表示する

  1. wordpressのマルチサイトで各サイトの記事を混ぜて表示する方法を考える
  2. MySQL – サイト毎に分かれている投稿テーブルを1つにまとめる
  3. MySQL – ブログIDを追加する
  4. MySQL – 1つにまとめたデータを投稿日順に並び替える
  5. MySQL – 1つにまとめたデータに取得件数を指定する
  6. 完成版(ページングなし) - wordpressのマルチサイトで各サイトの記事を混ぜて一覧表示する

参考にさせていただきました


スポンサーリンク