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:
1 |
ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
Change table collation:
1 |
ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
Change column collation:
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
1 2 3 |
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
1 2 3 |
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
1 2 3 |
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
How to change the collation of the database, table, column?