phpmysqlencodingutf8mb4

Working with SET NAMES utf8mb4 with utf8 tables

In a large system based on Mysql 5.5.57 Php 5.6.37 setup

Currently the whole system is working in utf8 including SET NAMES utf8 at the beginning of each db connection.

I need to support emojis in one of the tables so I need to switch it to utf8mb4. I don't want to switch other tables.

My question is - if I change to SET NAMES utf8mb4 for all connections (utf8 and utf8mb4) and switch the specific table only to utf8mb4 (and only write mb4 data to this table). Will the rest of the system work as before?

Can there be any issue from working with SET NAMES utf8mb4 in the utf8 tables/data/connections?

Solution

  • I think there should no problem using SET NAMES utf8mb4 for all connections.

    (utf8mb3 is a synonym of utf8 in MySQL; I'll use the former for clarity.)

    utf8mb3 is a subset of utf8mb4, so your client's bytes will be happy either way (except for Emoji, Egyptian hieroglyphs, and chess pieces which needs utf8mb4). When the bytes get to (or come from) a column that is declared only there will be a check to verify that you are not storing Emoji or certain Chinese characters, but otherwise, it goes through with minimal fuss.

    I suggest

     ALTER TABLE ... CONVERT TO utf8mb4

    as the 'right' way to convert a table. However, it converts all varchar/text columns. This may be bad...

    If you JOIN a converted table to an unconverted table, then you will be trying to compare a utf8mb3 string to a utf8mb4 string. MySQL will throw up its hands and convert all rows from one to the other. That is no INDEX will be useful.

    So... Be sure to at least be consistent about any columns that are involved in JOINs.