[thelist] [MySQL] Update Collation on All Tables

Max Schwanekamp lists at neptunewebworks.com
Sun Jan 22 16:48:45 CST 2006

Hmm, since I got no responses to this, perhaps I should rephrase it. 
How can I alter some columns on all tables across a DB in MySQL, on an 
automated basis?  Specifically, I want to change the default charset and 
collation on all tables and for each column that is of a textual 
(varchar, char, text, etc) type.  Obviously I can manually do the change 
via command-line or a frontend such as phpMyAdmin, but that's tedious 
and prone to error.  I cannot seem to find how to do this via the MySQL 
docs or Google, but maybe I'm using the wrong search terms.

I wrote:
> It's tedious to manually update each table's collation.  Can someone 
> point me to info describing how to update the collations of all columns 
> in all tables across an entire db?  I have a some latin1_swedish_ci 
> tables and utf8 tables mixed with mostly latin1_general_ci.  After 
> experiencing an "ILLEGAL MIX OF COLLATIONS" error, I want to standardize 
> collations across all tables.  Changing the collation for the db does 
> not affect the child tables, nor does doing th same for a table affect 
> the columns...arrgh.  So, I have to update every column, table and the 
> db itself to the same collation individually.  Suggestions?

Max Schwanekamp

More information about the thelist mailing list