Babydead Babydead - 1 year ago 100
MySQL Question

Handling mixed utf8 and utf8mb4 MYSQLI & PHP

Just now, I ran into a problem that I by mere chance had not encountered before:

In order to support emoji's in specific columns, I had decided to set my

and a few columns encoding within my database as well.

Now, I ran into the problem of PHP actually not correctly handling accented characters coming from normal
encoded fields.

Now, I'm stuck with having mixed
results. Since my data-handling is not very strong (used to work frameworks that handled it all for me) I'm quite unfamiliar with how I could best resolve this.

I have thought about the following options:

1 ) set my entire database to
collation instead of
with a few exceptions.

2 ) use
to change it, and simply make sure the queries getting said data are seperated

Now, neither of these seem like great ideas to me, but I can't really think of any better solution.

So then there's the remaining questions:

  • Will setting my entire db to
    instead of
    be a big performance change? I do realise that
    is bigger and therefore slower, which is why I tried to only use it on the columns in question in the first place.

  • Is there a way for me to simply have PHP handle
    encoding well, even when the
    is on

  • Do you have a better idea?

I'm at a real loss on this subject and I honestly can't guess which option is best. Googling on it didn't help too much as it only returned links explaining the differences of it or on how to convert your database to
, so I would very much love to hear the thoughts on this of one of the wise SO colleagues!

Columns in this specific case:

enter image description here

My response including PHP's character encoding detection:

arri�n = UTF-8
bolsward = ASCII
go�nga = UTF-8
lo�nga = UTF-8
echt = ASCII
echteld = ASCII
echten (drenthe) = ASCII
echten (friesland) = ASCII
echtenerbrug = ASCII
echterbosch = ASCII

My MYSQLI charset:
mysqli_set_charset($this->getConn(), "utf8mb4");

-- and I just realised the problem was with my
. there indeed used to be an underscore in there...

Answer Source

It is spelled utf8mb4 (no underscore).

See Trouble with utf8 characters; what I see is not what I stored . In particular, read "Overview of what you should do" in the answer.

You do not need to change the entire db. It is fine to specify utf8mb4 for only selected columns.

You do need to use utf8mb4 for the connection, but you specify 'UTF-8', which is the outside world's equivalent of MySQL's utf8mb4. MySQL's utf8 is a subset of utf8mb4. (Note: I am being precise in use of hyphens and underscores.)

utf8mb4 is not bigger, nor slower for transferring characters that are in common between utf8mb4 and the utf8 subset. Emoji are 4 bytes, so they are bigger than most other characters, but you are stuck with them being 4 bytes; don't sweat it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download