Query that convert the entire database's collation to utf8_general_ci

[Mgccl]

anyone got a query that can change every table and every field's collation that is latin1_swedish_ci to utf8_general_ci?
I have a lot tables and fields and I need to do it manually one by one in phpmyadmin, that could cost me like 3 days.

BTW it's mysql version 4.1

thx in advance 🙂

[Roger_Ramjet]

Well you do it with ALTER TABLE but you had better read the manual and notes first. Watch out for the behaviour change with 4.1.2

[MarkR]

Of course you'll want to test this very thoroughly.

I recommend using mysqldump twice, once to dump the schema, and again to dump the data.

Then do a search / replace on the schema file to change the encodings to utf8.

Then reload the schema.

Then reload the data.

Of course your web application will have to be shut down during this process - there is no way you can continue to allow writes during it.

As you'll want to EXTENSIVELY test your application to ensure it's working correctly with utf8, I recommend that you carry this all out on your development server and (if possible) get some beta testing on the site (on a staging server or something) to ensure it's working as expected.

If public beta testing of your application is unacceptable, consider getting in-house staff to do it.

This sort of change is bound to cause problems - but it's a very good idea.

Good luck.

Mark