-1

I have a database with a table called posts and I have two fields called title and description. Both title and description can contain emoji characters.

The solutions to similar problems told me to convert the character set to utf8mb4 and collate to utf8mb4_bin and also change the column type from text to NVARCHAR(255). I've even tried different combinations of using utf8, utf8mb4, utf8mb4_unicode_ci and utf8mb4_general_ci. They all don't work in displaying emojis.

error

Instead, this error will pop up once I try displaying the emojis. Anyone have a work around this?

I tried using different combinations of unicodes and collations to the database, but all don't seem to work.

Bob
  • 561
  • 5
  • 17
  • `utf8` will not work; all the `utf8mb4` things you tried should have worked. Please provide `SELECT title, HEX(title) FROM ...` for one of the rows where it is not working. – Rick James Jan 16 '23 at 19:48
  • @RickJames I used `SELECT title, description, timestamp FROM posts WHERE posts.id=%s;`. – Bob Jan 17 '23 at 04:00
  • I need to see the HEX to verify that it was stored correctly. Meanwhile, `utf8mb4` is required for Emoji. An incorrect way of "converging" could have mangled the data. How was the "convert" done? See also https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James May 01 '23 at 17:30

1 Answers1

0

I solved my problem. I had to make sure the charset connection and the collation connection was set to utf8mb4 and utf8mb4_unicode_ci respectively. I did this with the Flask-MySQLdb library.

from flask_mysqldb import MySQL

app = Flask(__name__)
app.config['MYSQL_HOST'] = 'host'
app.config['MYSQL_USER'] = 'user'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'db_name'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
app.config['MYSQL_CHARSET'] = 'utf8mb4'
app.config['MYSQL_COLLATION'] = 'utf8mb4_unicode_ci'

mysql = MySQL(app)
Bob
  • 561
  • 5
  • 17