0

I already changed my table and column collation to utf8mb4_bin. When making the connection, I passed the utf8 charset value mysql.connector.connect(host="localhost", user="root", password="****", database="dbname", charset='utf8') I even added the following statement right after opening the connection:

mycursor = mydb.cursor()
mycursor.execute("SET NAMES utf8;")
mydb.commit()

It only works partially. For example, this emoji is displayed in the table as you see it here: ⚡️

But these (a few examples): are displayed in table as ???? (4 question marks).

What did I forget to set or change?

miran80
  • 945
  • 7
  • 22
  • 1
    https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-mysql-database – stefan_aus_hannover Sep 22 '21 at 17:25
  • 1
    Does the utf8 charset differ from utf8mb4? The lightning bolt in the BMP; the smiley face is not in the BMP and requires 4 bytes to encode in UTF-8. (And that is indeed an issue, per a quick web search — see https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql). – user2864740 Sep 22 '21 at 17:28
  • 1
    That is, try to use `utf8mb4` in the table *and* connection *and* SET NAMES. – user2864740 Sep 22 '21 at 17:32
  • 1
    Thank you guys, setting `charset='utf8mb4'` in `mysql.connector.connect()` fixed it. I don't know why I only had `utf8`. I was able to also remove `mycursor.execute("SET NAMES utf8mb4;")` as it was redundant. – miran80 Sep 22 '21 at 17:37
  • Just a quick thought: mysql-connector doesn't let me pass `utf8mb4_unicode_ci` as charset parameter when setting up connection. If it did, would using `utf8mb4_unicode_ci` be better than `utf8mb4`? – miran80 Sep 22 '21 at 17:50
  • 1
    UTF-8 (well, when done correctly) is UTF-8 (an encoding of Unicode to bytes). The collation in the table *also* affects ordering and comparing (such as Case-Insensitive); however it has no effect on the encoding (the UTF-8 byte representation, when done correctly). – user2864740 Sep 22 '21 at 18:49

0 Answers0