I'm in the progress of migrating my database from latin1 to utf8 character sets/collations.

I wrote a simple script that loops through all of my tables and executes this statement:

ALTER TABLE {database}.{table} CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

This works great for almost all of the tables, but I noticed there are a few tables that fail to convert - the ALTER reports successful and there were no errors, but the collation remains as latin1.

Upon further inspection, I noticed that this particular table only has Integer columns, so perhaps this is the reason? But shouldn't that still at least change the table collation in the event I add VARCHARs later?

** edit **

I used phpMyAdmin's UI to convert the table and it gave me a slightly different command:

ALTER TABLE {table} DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

What is the difference between these to statements; why would one work and not the other?

migrated from serverfault.com Apr 22 '14 at 2:20

This question came from our site for system and network administrators.

up vote 1 down vote accepted

The ALTER TABLE ... DEFAULT CHARACTER SET ... command sets the default character set and collation for the table. It does not affect the existing VARCHAR/TEXT columns and their data. Only newly created fields will use the specified charset and collation.

The ALTER TABLE ... CONVERT TO CHARACTER SET ... command changes the character sets and collations of all the existing VARCHAR/TEXT fields (and converts the data). It actually should change the default charset for the table as well. It is not specified obvious enough here: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html but it works in that way. On my MySQL 5.1.45 the default charset is changed by the CONVERT operation even it does not include VARCHAR-fields. Also the CONVERT operation may change the datatype if it cannot store the new length of data.

  • Thanks for the explanation. No idea why MySQL 5.6 won't change the default charset for CONVERT operations like it should... I'll just apply the other ALTER statement as needed. – DOOManiac Apr 22 '14 at 16:08

Namaste answered it April 22nd 2014. My recent experience (to clarify where CONVERT seems to silently fail) is that IF the table did not include any text based columns the CONVERT would not add the collation to the table definition. Alter table syntax would appear to succeed and not show any warnings.

Syntax: ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Using DEFAULT CHARACTER SET in that situation did make sure that the the output of show create table the COLLATE field showed up as "COLLATE=utf8_unicode_ci"

Syntax: ALTER TABLE tablename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.