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
