HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Do I need to convert varchar(32) and varcha(64) to update from MySQL 5.7 to 8.0

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
updateneedconvertvarcharvarchamysqlandfrom

Problem

I am upgrading a database from 5.7 to 8.0 in MySQL and ran across this:
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

I understand converting the schema and tables to utf8mb4 and utf8mb4_unicode_ci but regarding the changing of columns to varchar(191) I was lost. I tried to follow their disclaimer and read the notes but I cannot see if this is actually needed to upgrade...or why it is even listed as a step.

Also, are there any issues that could arise from converting the tables to utf8mb4 from utf8 and just skipping converting the columns to a different type?

Solution

No you can change the character set, because you don't come to the limit that is explained in the article.

The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 character.

So if your utf8 column varchar has a size of more than 191 characters, you will get an error when converting, because tf8mb4 needs 4 bytes per characters and that will exceed the limit.

But as you only have 32 and 64 characters you are wide blow the limit of 191

Context

StackExchange Database Administrators Q#275553, answer score: 2

Revisions (0)

No revisions yet.