0

I have a MySQL 5.5.31 database which has approx 220 tables - of these 220 tables, around half of them are already using utf8mb4_unicode_ci but the "older" tables are still using latin1_swedish_ci. The fields in the tables are a mix of integer, varchar, longtext, date, datetime and decimal and there are no views or stored procedures.

I now have a requirement to support Russian text so I need to convert the remaining tables to UTF8. Based on my reading, I have opted for utf8mb4_unicode_ci as I believe it offers the broadest support including Chinese.

I have tested as much as I can with individual tables in a test environment without any issues and now need to bite the bullet and do this on my production database - I will, of course, take a full backup just before I make any changes.

I have read of some issues with converting TEXT and CHAR fields as well as some considerations on index sizes for VARCHAR fields but nothing else significant.

I know how to do this but I wanted a final sanity check to make sure there is nothing major I am missing before taking the plunge?

1

I assume you will do this for each table? ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

One potential issue involves the maximum of 767 bytes per column in an INDEX. If you currently have a VARHAR(255) latin1 field in an index, you will need to rethink it.

  • Decrease it to VARCHAR(191) if you are sure that 191 will suffice (into the future)
  • Use a 'prefix' index: INDEX(foo(191)). This is often not worth doing.
  • DROP the index. (But not if you need the index; let's see the SELECT that needs it.)

Edit

For shrinking to VARCHAR(191), you could write a Stored Procedure to look in information_schema to discover all the VARCHARs, and create and execute SELECT MAX(CHAR_LENGTH(col)) ... to see if any are over 191 (or even dangerously close to 191). You might be surprised to find a few with max=255, implying that you may already have truncated some text.

The other two options won't damage data, but could have performance problems.

  • Yes that is the plan - my main concern is that I don't impact any existing data. I have millions of rows across 200 odd tables and I need to ensure the integrity of that data post changes. Like I said before, I have tried this in dev with no issues but now I have to bite the bullet I am nervous in case it impacts customer data – bhttoan Apr 13 '15 at 13:07
  • added an edit... – Rick James Apr 13 '15 at 15:17

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.