Cara merubah collation mysql tabel dan kolom secara bersamaan

Langkah mudah untuk merubah collation seluruh isi database mysql,
yang dirubah yaitu :
- database default collation
- table collation
- column collation

caranya sbb :

1. lakukan pengecekan

SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'imsdb' AND collation_name != 'utf8_unicode_ci';
SELECT * FROM information_schema.TABLES WHERE table_schema = 'imsdb' AND table_collation != 'utf8_unicode_ci';

2. rubah db nya
ALTER DATABASE imsdb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
SET FOREIGN_KEY_CHECKS = 0;

3. generate QUERY pengubah tabel lalu jalankan
SELECT
    CONCAT('ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;') -- INTO OUTFILE '/alterstatements.sql'
FROM
    information_schema.tables
WHERE
    table_schema='imsdb'
    -- AND table_collation != 'utf8_unicode_ci'
GROUP BY table_name;

3. generate QUERY pengubah COLUMNS lalu jalankan
--
SELECT
    CONCAT('ALTER TABLE ',  table_name,' CHANGE `',COLUMN_NAME, '` `',COLUMN_NAME, '` ',COLUMN_TYPE,' CHARSET utf8 COLLATE utf8_unicode_ci;') -- INTO OUTFILE '/alterstatements.sql'
FROM
    information_schema.COLUMNS
WHERE
    table_schema='imsdb'
    -- AND table_collation != 'utf8_unicode_ci'
GROUP BY table_name;

4. kembalikan fk checks
SET FOREIGN_KEY_CHECKS = 1;

selamat mencoba
arif