How to change database collation

Home » Server Software » MySQL Tweaks » How to change database collation
January 29, 2015 MySQL Tweaks, phpMyAdmin No Comments

Changing the database collation is rather easy and needed whenever the server default collation does not comply with the one required by your CMS or PHP script. This issue is pretty common among Moodle users, when upgrading and can be resolved following the steps below:

*** Changing via phpMyAdmin

1. For databases:
Log into phpMyAdmin.
Select your database from the list on the left.
Click on “Operations” from the top set of tabs.
In the Collation box, choose your new collation from the dropdown menu. utf8_general_ci is the most common utf8 collation.
Select your database from the list on the left.
Click on “Operations” from the top set of tabs,
In the Collation box, choose your new collation from the dropdown menu. utf8_general_ci is the most common utf8 collation.
Click Go.
2. For database tables:
To update a database table, select it from the list on the left.
Click on “Operations” from the top set of tabs.
In “Table options,” under “Collation,” choose your new collation from the dropdown menu.
Click Go.

*** Alternatively you can also change the collation of your database and tables by executing SQL queries in the SQL command field or by doing them via terminal after connecting to the MySQL server:

1. For databases:
Run the following command to change the character set and collation of your database:
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
2. For database tables:
Run the following command to change the character set and collation of your table:
ALTER TABLE tablename CHARACTER SET utf8 COLLATE utf8_general_ci;
For more information about database collation, please also read the MySQL documentation: http://dev.mysql.com/doc/refman/5

LEAVE A COMMENT