Recently, I work on a Flask Web App with some CRUD manipulations. A MySQL ORM, called Flask-SQLAlchemy is used to create MySQL models and interact with databases. However, the problem happended when I was trying to create a new entry with some Chinese characters in its columns.
Not the exact error output I have. But it's pretty similar After searching for a few similar problems, I found the issue is the encoding format of MySQL database.
The default encoding format of MySQL is actually Latin-1. To allow characters in other languages, like Chinese characters, we have to switch it to UTF-8.
Therefore, after we crate a new database, the first thing we should do normally is to change its encoding configs to UTF-8. For example:
set character_set_client=utf8;
set character_set_results=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
However, this is not the whole story. If we check one of the tables in this database, we could find that the configs are like this:
See, the encoding format of this table is still Latin-1. So what we could know is, those tables which are created before we switch the encoding format of the database would not be changed. So we have to use the following sentence to modify the encoding format of the table:
alter table BETWEENNESS character set utf8;Unfortunately, the error is raised once again after this change. Checking the table again, we notice that even though the table now uses UTF-8 for encoding, some columns are still in Latin-1.
We use the following sentences fix the issue:
alter database matrix character set utf8;
alter table BETWEENNESS change semantic semantic varchar(32) character set utf8 not null;
alter table BETWEENNESS character set utf8;
This time, there is no more Latin-1 shows up in our table. The issue is eventually solved.
In conclusion, other than changing those default configs of the database, the following two sentences could help you to modify the tables and columns that are already created and set with some other encoding formats:
alter table <TABLE> character set utf8;
alter table <TABLE> change <COLUMN> <COLUMN> <TYPE> character set utf8;