-- この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を空にしておく って作業履歴所に書いてあった