Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database?
I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will post it myself.
Be careful! If you actually have utf stored as another encoding, you could have a real mess on your hands. Back up first. Then try some of the standard methods:
for instance http://www.cesspit.net/drupal/node/898 http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html
I've had to resort to converting all text fields to binary, then back to varchar/text. This has saved my ass.
I had data is UTF8, stored as latin1. What I did:
Drop indexes. Convert fields to binary. Convert to utf8-general ci
If your on LAMP, don’t forget to add set NAMES command before interacting with the db, and make sure you set character encoding headers.