I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in that table which has latin1 character encoding. I want to change it to utf8. Since I have a very large amount of data, I was thinking if there is some way I can create new partition with that column encoded in utf8 and leave the old partitions as they are. They will get deleted at some point of time in future and I will have whole data encoded properly.

Is there any way of doing this?

  • I'm pretty sure that this isn't possible, but I can't find an explicit reference describing what you're trying to do that says so. – Phil Sumner Jul 9 '14 at 14:26

Partitions have in their syntax the option to have different engines for every partition, but this is not (yet?) supported.

Having different character sets for different partitions, however, may not make sense as all of them should have a common data structure. It is also not supported. Please understand that the charset is a property of a field, not of a table, and that the only reason you can configure it for a table is to set a "default".

If you want to create partitions for archiving, that is a good call, as it makes easier to delete old ones. Be aware of MySQL partitioning restrictions.

If you want to change the charset of a field in an online fashion, I recommend you to use any online alter table tool (I think that MySQL 5.6 does not allow for online charset alters, but I may be wrong) like Facebook's, Percona's or Shlomi's.

As an addendum, if at some point you need to mix different charsets on a field (I have needed it in the past as an intermediate step for having invalid charsets), you could set the type of the string to binary type instead of text (BLOB instead of TEXT, VARBINARY instead of VARCHARetc.). Binary strings do not have encoding information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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