A script to change all tables and fields to the utf-8-bin collation in MYSQL


Question

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.

1
56
6/5/2015 6:44:26 PM

Accepted Answer

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.

23
9/19/2008 9:01:16 PM

Can be done in a single command (rather than 148 of PHP):

mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &

You've got to love the commandline... (You might need to employ the --user and --password options for mysql).

EDIT: to avoid foreign key problems, added SET foreign_key_checks = 0; and SET foreign_key_checks = 1;


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon