Změna CHARSET a COLLATION u celé MySQL databáze

Pokud potřebujete změnit CHARSET a COLLATION na něco jednotného u databáze, všech tabulek a u veškerých VARCHAR a (MEDIUM)TEXT fieldů, tento jednoduchý skript to zajistí.

Výsledek dotazu stačí spustit :-)

-- database name to run script on
SET @DBNAME = 'syntex';
-- requested charset
SET @CHSET = 'utf8mb4';
-- requested collation
SET @COLL = 'utf8mb4_general_ci';

-- DATABASE collation
SELECT 
 CONCAT('ALTER DATABASE `',@DBNAME,'` CHARACTER SET ',@CHSET,' COLLATE ',@COLL,';') as _sql

UNION ALL

-- TABLE collations
SELECT 
 CONCAT('ALTER TABLE `',  T.table_name, '` CHARACTER SET ',@CHSET,' COLLATE ',@COLL,';') 
 as _sql
FROM information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
JOIN information_schema.TABLES AS T ON T.table_collation = C.collation_name
WHERE T.table_schema = @DBNAME
 AND (C.CHARACTER_SET_NAME != @CHSET OR C.COLLATION_NAME != @COLL)

UNION ALL

-- VARCHAR and TEXT fields collations
SELECT CONCAT(
 'ALTER TABLE `', c.table_name, '` MODIFY `', c.column_name, '` ', c.DATA_TYPE, 
 IF(c.DATA_TYPE = 'varchar', CONCAT( '(', c.CHARACTER_MAXIMUM_LENGTH, ')'), ''),
 ' CHARACTER SET ',@CHSET,' COLLATE ', @COLL,
 (CASE WHEN c.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';' ) 
 as _sql
FROM information_schema.COLUMNS c
JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.TABLE_SCHEMA = @DBNAME
 AND t.TABLE_TYPE LIKE '%TABLE%'
 AND (c.DATA_TYPE = 'varchar' OR c.DATA_TYPE LIKE '%text%')
 AND (c.CHARACTER_SET_NAME != @CHSET OR c.COLLATION_NAME != @COLL);