0

I'm new to MySQL database. I want to store emojis in my database. I already have data in my database, and I don't want to lose it.

I don't know the proper way of storing the utf8mb4 character-set using the MySQL console. Is there any way of doing that using mysql-administrator?

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 5.7.33                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2 |
| version                 | 5.7.33-log            |

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR 
Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8               |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

I changed my.cnf file to

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

It shows no error, but neither does it store the emojis - when enter emojis it shows as ? this question mark.

I convert the utf-8 encoding to utf8mb4_general_ci:

|--------------------------+--------------------|
| character_set_system     | utf8               |
|--------------------------+--------------------|
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
|--------------------------+--------------------|
4
  • The connection matters not if the storage collation does not support 4-byte characters. If your database tables are all using some latin or general collation, you’re boned. These collations only support 1-byte characters (and sometimes 3-byte). This is why I asked the question.
    – matigo
    May 15 at 15:40
  • @matigo can it be changed? May 15 at 15:46
  • Its just software. You control the software. Yes, it can be changed so long as the effort is something worth investing in. The question i asked earlier was looking for a CREATE statement for the table(s), as this would determine if a change is necessary at all or if the problem is elsewhere in the stack. Modern software is built like a house of cards. When done right, it’s an amazing sight to behold. If just one spectator sneezes, though …
    – matigo
    May 15 at 15:50
  • well as i described the full problem in stack overflow. i'm using django and my tables are created by models field by class based views May 15 at 16:02
1

Do SHOW CREATE TABLE for one of the tables where you need Emoji. It probably says 'utf8' on the columns and/or default for the table. The columns must be utf8mb4.

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

Will blindly change all the columns to utf8mb4. This is probably the action you need.

Also, the connection needs to specify utf8mb4. Since I don't know what client you are using, I can't provide specific information. If necessary, run this right after connection: SET NAMES utf8mb4;

Terminology:

  • "character set", such as utf8 or utf8mb4 is the encoding for the characters. This is where you are having trouble with storing Emoji.
  • "collation" is the ordering, such as whether 'a' < 'b' and whether there is case folding to make 'a' = 'A'. Collation may be important when comparing (WHERE) or sorting (ORDER BY).
2
  • I already tried these commands. I wil try again with these commands. May 17 at 8:21
  • Thank you its working May 17 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.