Home arrow Blog arrow How to change collation for all columns in a MySQL table

How to change collation for all columns in a MySQL table

In principle, it is possible to change the character set for all the fields in a table. But this can give problems and needs to be tackled with care. The table should certainly be backed up before attempting the change.

But it is straightforward to change the collation when the character set is staying the same. Do it with the following SQL statement:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET 
charset_name COLLATE collation_name;

Substitute your own table name, restate the character set (e.g. utf8) and specify the desired collation e.g. utf8_general_ci.

#128001 • 02/27/2011 5:01pm by Martin Brampton • Vote: Up votes (226) Down votes (105)