CloudSQL(MySQL)でutf8mb4を使う

概要

ユーザの投稿時に絵文字 (😁) を入れたら、SQL内でエラーが起きた。
その対応のために、テーブルに保存するには charset = utf8mb4 という文字コードにしないといけない。
かつ、それを検索するためには、 collate = utf8mb4_bin にする。
collateの値については試行錯誤していたのもあり、確実ではないが、他のblogなどを色々参照していくと、binにするのが良さそうということ、たしかに検索もできた。

前提

すでに以下のようなテーブルがあり、この中のnameに絵文字が入るという要件があるとする

CREATE TABLE `users` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `photo_url` varchar(255) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

環境

CloudSQL(MySQL 第2世代 5.7)

やり方

1. 対象のカラムのcharsetとcollateを変更する
2. プログラムの方でdbにアクセスする際にcharsetを utf8mb4 にする
   あとは

• テーブルのデフォルトの文字列を変更する
• データベースのデフォルトの文字列を変更する
  などの処理をしたほうが後々はいいが、ここでは割愛する（そんな全部絵文字対象ですか？ってこと + CloudSQLでの設定がよくわからん

1. 対象のカラムのcharsetを変更する

charset を utf8mb4、 collate を utf8mb4_bin にする

alter table users modify name varchar(255) character set utf8mb4 COLLATE utf8mb4_bin;

以下のようになっていればOK

mysql> show create table users;

CREATE TABLE `users` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `photo_url` varchar(255) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. CloudSQLにアクセスする charsetを utf8mb4 にする

Goでsqlxを使っている例であるが、他の言語でもあまり変わりはないはず
charset=utf8mb4 にする

db, err := sqlx.Connect("mysql", fmt.Sprintf("%s:%s@unix(%s/%s)/%s?charset=utf8mb4", user, password, socDir, connectionName, databaseName))

まとめ

とりあえずutf8mb4を使うとデータを入れることができ、また utf8mb4_bin を利用すると検索ができる。

utf8mb4_bin の他にいろいろあるが、果たして一番何が正しいのかがまだわかっていない（indexを貼るような場合が鍵なはず

まとめてくださっていた。厳密な検索をしたいならbinでいいですね。
https://qiita.com/tfunato/items/e48ad0a37b8244a788f6

その他

ちなみにDBの方だけ対応している場合は以下のようにエラーになる(in句に顔文字を入れた場合

Error 1271: Illegal mix of collations for operation ' IN '

ローカルからCloudSQLProxyを使って、CloudSQLにアクセスする方法が便利
https://cloud.google.com/sql/docs/mysql/sql-proxy?hl=ja