yokuさんにutf8→utf8mb4binのときに教えてもらった便利Tips

UTF8toUTF8mb4bin.md

確認用便利SQL

-- このQueryでは文字列型のカラムがないテーブルは引っ掛からないので注意
SELECT table_schema,
       table_name,
       COUNT(DISTINCT collation_name)                                AS _c,
       GROUP_CONCAT(DISTINCT collation_name ORDER BY collation_name) AS _collations
FROM information_schema.columns
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND collation_name IS NOT NULL
GROUP BY table_schema, table_name;

-- Collationが複数あり、単純にワンライナーで置換できないテーブル一覧
SELECT table_schema,
       table_name,
       COUNT(DISTINCT collation_name)                                AS _c,
       GROUP_CONCAT(DISTINCT collation_name ORDER BY collation_name) AS _collations
FROM information_schema.columns
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND collation_name IS NOT NULL
GROUP BY table_schema, table_name
HAVING _c > 1;

-- utf8_general_ciとutf8_binのそれぞれのテーブル数
SELECT _collations, COUNT(*)
FROM (SELECT table_schema,
             table_name,
             COUNT(DISTINCT collation_name)                                AS _c,
             GROUP_CONCAT(DISTINCT collation_name ORDER BY collation_name) AS _collations
      FROM information_schema.columns
      WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
        AND collation_name IS NOT NULL
      GROUP BY table_schema, table_name
      HAVING _c = 1) AS dummy
GROUP BY 1;


SELECT table_schema, table_name, column_name, collation_name
FROM information_schema.columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
                                     FROM (SELECT table_schema,
                                                  table_name,
                                                  COUNT(DISTINCT collation_name)                                AS _c,
                                                  GROUP_CONCAT(DISTINCT collation_name ORDER BY collation_name) AS _collations
                                           FROM information_schema.columns
                                           WHERE table_schema NOT IN
                                                 ('mysql', 'information_schema', 'performance_schema', 'sys')
                                             AND collation_name IS NOT NULL
                                           GROUP BY table_schema, table_name
                                           HAVING _c > 1) AS dummy)
  AND collation_name LIKE '%bin';
SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND table_collation NOT LIKE 'utf8mb4\_%';
SELECT table_schema, table_name, collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND collation_name IS NOT NULL
  AND collation_name NOT LIKE 'utf8mb4\_%';

ハマり箇所

切り詰められ方が変わることがあるので注意する感じですかね
https://yoku0825.blogspot.com/2020/06/utf8mb3.html
yoku0825.blogspot.comyoku0825.blogspot.com

あとは ALTER DATABASE .. CHARSET utf8mb4 忘れがち問題

ALTER TABLE .. CONVERT TO CHARSET utf8mb4 COLLATE .. は1つしかコレーションを指定できないので、1テーブルに *_ci と *_bin が混在している場合 CONVERT TO CHARSET してからもう一度 MODIFY してやらないといけない問題

5.6とそれ以前で default_row_format が指定できないところは、一緒に ROW_FORMAT= Dynamic を指定しないとエラーになるかもしれませぬ

- 既にINSERT済の NO_ZERO_DATE がエラーになるので、CONVERTするインスタンスではsql_modeを空にしておく って作業履歴所に書いてあった

読んどけ

• https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0040
• https://speakerdeck.com/_awache/mysql-casual-talks-13-change-character-set