MySQLに絵文字をINSERTする時にエラーが発生するときの対応方法

カラム text に絵文字が入ることがあり、そのままではMySQLに入らなかったのでその対応方法をメモ

my.cnf

[client]
   default-character-set = utf8mb4
[mysqld]
   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
   character-set-server = utf8mb4
   collation-server = utf8mb4_unicode_ci
   skip-character-set-client-handshake

を追加して、MySQLを再起動
※ skip-character-set-client-handshake は、SQLインジェクションになりかねないとのことで推奨しないという話もある

CREATE TABLE `qc_abematv_deletetime` (
  `userID` bigint(20) DEFAULT NULL,
  `messageID` varchar(255) NOT NULL PRIMARY KEY,
  `title` varchar(128),
  `text` text,
  `status` varchar(16),
  `categoryName` varchar(128),
  `postTime` datetime NOT NULL,
  `lastOperate` datetime NOT NULL,
  `operatorName` varchar(64),
  `subcategoryName` varchar(32),
  `penaltyCategoryName` varchar(32),
  INDEX index_lastOperate(lastOperate),
  INDEX index_postTime(postTime),
  INDEX index_status(status),
  INDEX index_categoryName(categoryName),
  INDEX index_title(title),
  INDEX index_operatorName(operatorName),
  INDEX index_subcaegoryName(subcategoryName),
  INDEX index_penaltyCategoryName(penaltyCategoryName),
  INDEX index_multi1(lastOperate, categoryName)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

$ mysql orion -uroot < create_table.sql
ERROR 1071 (42000) at line 2: Specified key was too long; max key length is 1000 bytes

messageID varchar(255)

が問題で、messageID varchar(250) にしたら問題なくなった

ちなみに、テーブル作成のオプションを

ENGINE=MyISAM DEFAULT CHARSET=utf8

にすると varchar(255) でも問題なくINDEX作成できた
messageID varchar(333) はOK、messageID varchar(334) はNG

ここからインデックスサイズは
- UTF8だとVARCHAR(N)とすると　N*3
- UTF8MB4だとVARCHAR(N)とすると　N*4
- インデックスサイズのリミットは1000（デフォルト）

これだとフィールド長をかえられない場合は詰んでしまう。
そこで

CREATE TABLE `qc_abematv_deletetime` (
  `userID` bigint(20) DEFAULT NULL,
  `messageID` varchar(333) CHARACTER SET utf8 NOT NULL PRIMARY KEY,
  (略）
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

とすれば問題なくいけた。
テーブルに文字コードを設定する方法

なお、varchar(333)をvarchar(334)にするとエラーが発生した。

最後に

ALTER DATABASE tb_name DEFAULT character set utf8mb4 COLLATE utf8mb4_bin;

も必須