0

This question is about existing tables. Changing the default for a database is well covered.

Most questions here on Stack Overflow seems to assume that you want to change both the character set and the collation - say from latin1_general_ci to utf8_spanish_ci. The answers typically recommends:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_spanish_ci;

This seems like a waste when the character set is unchanged - only the collation should be updated - but maybe MySQL is clever enough so it discovers that the character set is the same and no resources are wasted?

The question becomes: If only the collation is changing, what is the recommended procedure to update it for existing tables (and their columns)?

hansfn
  • 530
  • 4
  • 18
  • 1
    If the `ALTER TABLE` above works, then what is the problem? Do you really think performance is an issue here? – Tim Biegeleisen Feb 06 '17 at 01:09
  • Yes, I wouldn't have asked the question if not. Converting a lot of data from one character set to another is a lot of work. If you can only change the metadata of the tables and columns, that is much better. Related: Looking at http://stackoverflow.com/questions/5906585 the recommended answer uses "ALTER TABLE CONVERT TO CHARACTER SET ..." and then "ALTER TABLE MODIFY ..." which should not be necessary according to https://dev.mysql.com/doc/refman/5.5/en/alter-table.html I guess I'm just looking for some clear answers. – hansfn Feb 06 '17 at 01:27
  • To follow up myself: I just did a small test with a single database. Made two copies and ran "ALTER TABLE CONVERT TO CHARACTER SET ..." on all the tables of one database and "ALTER TABLE COLLATE ..." on all the tables of the other database. The first took 22 seconds while the second took 21 seconds. It seems my concerns are wasted. If someone can confirm this, it would be nice. – hansfn Feb 06 '17 at 21:23

1 Answers1

1

I think the answer is:

For each table, do

ALTER TABLE <table_name> COLLATE utf8_spanish_ci;

For each column, do

ALTER TABLE <table_name> MODIFY <column_name> <column_type> COLLATE utf8_spanish_ci;

You can use the information_schema database to generate the SQL to run - for just the needed tables

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` COLLATE utf8_spanish_ci;') 
from information_schema.tables 
where TABLE_SCHEMA like 'database_name' and TABLE_COLLATION = 'utf8_general_ci';

and columns

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` MODIFY `', column_name, '` ', COLUMN_TYPE, ' COLLATE utf8_spanish_ci;') 
from information_schema.columns 
where TABLE_SCHEMA like 'database_name' and COLLATION_NAME = 'utf8_general_ci';
hansfn
  • 530
  • 4
  • 18