Can I safely use a utf8 connection with utf8mb4 columns?

1k views Asked by At

As long as all tables in my database are not migrated to utf8mb4, is there a risk of continuing to use a utf8 connection?

I thought keeping the utf8 connection until all the tables are migrated.

The underlying question is: how long will it take to make changes on very large tables (hundreds of GB)?

2

There are 2 answers

0
Rick James On BEST ANSWER

As long as all the data is in the 3-byte utf8 subset, utf8 and utf8mb4 are identical.

The connection parameters (including SET NAMES) specify the encoding in the client. The column/table definition says what encoding is handled in the columns. MySQL will convert between those as you INSERT or SELECT. Again, as long as all the data is utf8 subset, the conversion is possible (effectively a no-op).

1
Guillaume On

Some examples of how MySQL performs the conversion if you trying to insert the sentence :
« This emoji is a cat »

1 - with a utf8 connection in a table utf8 : MySQL truncates the string from character 4 bytes:
« This emoji »

2 - with a utf8 connection in a utf8mb4 table : MySQL replaces the 4 bytes character with "???? "
« This emoji ???? is a cat»

3 - with a utf8mb4 connection in a utf8 table : MySQL replaces the 4 bytes character with "? "
« This emoji ? a cat »