Change the default collation of MySQL database

PHP and MySQL is a popular pair for web development, but sometimes we need to change the default collation to display the right language and faster for MySQL, in this case, let’s check some of simple tips to do that.

Change database collation:

ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_unicode_ci;

1	ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Change table collation:

ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

1	ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Change column collation:

ALTER TABLE MODIFY VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

1	ALTER TABLE MODIFY VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Heres how to change all databases/tables/columns. Run these queries and they will output all of the subsequent queries necessary to convert your entire schema to utf8. Hope this helps!

Change DATABASE Default Collation

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'database_name';

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')

from information_schema.tables

where TABLE_SCHEMA like 'database_name';

Change TABLE Collation / Char Set

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'database_name';

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')

from information_schema.tables

where TABLE_SCHEMA like 'database_name';

Change COLUMN Collation / Char Set

SELECT concat('ALTER TABLE `', t1.TABLE_SCHEMA, '`.`', t1.table_name, '` MODIFY `', t1.column_name, '` ', t1.data_type , '(' , t1.CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.columns t1
where t1.TABLE_SCHEMA like 'database_name' and t1.COLLATION_NAME = 'old_charset_name';

SELECT concat('ALTER TABLE `', t1.TABLE_SCHEMA, '`.`', t1.table_name, '` MODIFY `', t1.column_name, '` ', t1.data_type , '(' , t1.CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')

from information_schema.columns t1

where t1.TABLE_SCHEMA like 'database_name' and t1.COLLATION_NAME = 'old_charset_name';

Reference:

https://stackoverflow.com/questions/5906585/how-to-change-the-default-collation-of-a-database

https://stackoverflow.com/questions/1036454/what-are-the-diffrences-between-utf8-general-ci-and-utf8-unicode-ci