How to store Emoji Character in MySQL Database

Question

I am using Emoji character in my project  That characters are saved      into mysql database  I had used database Default collation in utf8mb4 general ci  It show      1366 Incorrect string value    xF0 x9F x98 x83 xF0 x9F     for column    comment  at row 1

User · Answer

I have a good solution to save your time. I also meet the same problem but I could not solve this problem by the first answer.

Your defualt character is utf-8. But emoji needs utf8mb4 to support it. If you have the permission to revise the configure file of mysql, you can follow this step.

Therefore, do this following step to upgrade your character set ( from utf-8 to utf8mb4).

step 1. open your my.cnf for mysql, add these following lines to your my.cnf.

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'

[mysql]
default-character-set = utf8mb4


[client]
default-character-set = utf8mb4

step2. stop your mysql service, and start mysql service

mysql.server stop
mysql.server start

Finished! Then you can check your character are changed into utf8mb4.

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------------------------------------+
| Variable_name            | Value                                                    |
+--------------------------+----------------------------------------------------------+
| character_set_client     | utf8mb4                                                  |
| character_set_connection | utf8mb4                                                  |
| character_set_database   | utf8mb4                                                  |
| character_set_filesystem | binary                                                   |
| character_set_results    | utf8mb4                                                  |
| character_set_server     | utf8mb4                                                  |
| character_set_system     | utf8                                                     |
| character_sets_dir       | /usr/local/Cellar/[email protected]/5.7.29/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------+
8 rows in set (0.00 sec)

User · Answer

I have updated my database and table to upgraded from utf8 to utf8mb4  But nothing works for me  Then I tried to update column datatype to blob  luckily it worked for me and data has been saved  Even my database and table both are CHARACTER SET utf8 COLLATE utf8 unicode

User · Answer

Well  you need not to change the Whole DB Charset  Instead of that you can do it by changing column to blob type   ALTER TABLE messages MODIFY content BLOB

User · Answer

Both the databases and tables should have character set utf8mb4 and collation utf8mb4 unicode ci   When creating a new database you should use     CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    If you have an existing database and you want to add support   ALTER DATABASE database name CHARACTER SET   utf8mb4 COLLATE   utf8mb4 unicode ci    You also need to set the correct character set and collation for your tables   CREATE TABLE IF NOT EXISTS table name             ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4 unicode ci    or change it if you ve got existing tables with a lot of data   ALTER TABLE table name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    Note that utf8 general ci is no longer recommended best practice  See the related Q  amp  A   What s the difference between utf8 general ci and utf8 unicode ci on Stack Overflow

User · Answer

The simplest solution what works for me is to store the data as json encode   later when you retrieve just make sure you json decode it   Here you don t have to change the collation or the character set of the database and the table

User · Answer

Emoji support for application having tech stack - mysql  java  springboot  hibernate  Apply below changes in mysql for unicode support    ALTER DATABASE  lt database-name gt  CHARACTER SET   utf8mb4 COLLATE   utf8mb4 unicode ci  ALTER TABLE  lt table-name gt  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    DB Connection - jdbc url change   jdbc mysql   localhost 3306  lt database-name gt  useUnicode yes amp characterEncoding UTF-8  Note - If the above step is not working please update mysql-connector version to 8 0 15   mysql 5 7 works with connector version 8 0 15 for unicode support

User · Answer

If you use command line interface for inserting sql file to database  Be sure your table charset utf8mb4 and column collation utf8mb4 unicode ci or utf8mb4 bin mysql -u root -p123456 my database  lt  profiles sql  ERROR 1366  HY000  at line 1679  Incorrect string value    xF0 x9F x98 x87 xF0 x9F     for column  note  at row 328 we can solve the problem with this parameter --default-character-set name  Set the default character set  mysql -u root -p123456 --default-character-set utf8mb4 my database  lt  profiles sql

User · Answer

The command to modify the column is   ALTER TABLE TABLE NAME MODIFY COLUMN NAME TYPE    And we need to use type   BLOB  Example to modify is as under -  ALTER TABLE messages MODIFY content BLOB    I checked that latest mySQL and other databases don t need    to use in command on table name  column name etc   Fetch and Save data  Directly save the chat content to column and to retrieve data  fetch data as byte array  byte    from db column and then convert it to string e g   Java code   new String  byte    arr

User · Answer

If you are inserting using PHP  and you have followed the various ALTER database and ALTER table options above  make sure your php connection s charset is utf8mb4  Example of connection string   this- gt pdo   new PDO  quot mysql host  ip port  port dbname  db charset utf8mb4 quot   etc etc  Notice the  quot charset quot  is utf8mb4  not just utf8

User · Answer

The main point hasn t been mentioned in the above answers that   We need to pass query string with the options  useUnicode yes  and  characterEncoding UTF-8  in connection string  Something like this  mysql   USERNAME PASSWORD HOSTNAME PORT DATABASE NAME useUnicode yes amp characterEncoding UTF-8

User · Answer

If you are using Solr   Mysql   Java  you can use   This can be Used     case1  When you don t want to alter DB  case2  when you have to import emoticons from your Mysql to Solr core    In above case this is one of the solutions to store your emoticons in your system   Steps to use it   Library used  import java net URLDecoder                import java net URLEncoder    Use urlEncoder to encode your String having emoticons  Store it in DB without altering the MysqlDB  You can store it in solr core decoded form if you want or you can store  encoded form  When fetching these emoticons from DB or Solr core you can now decode it  Using urlDecoder    Code example   import java net URLDecoder  import java net URLEncoder   public static void main String   args          SpringApplication run ParticipantApplication class  args       System out println encodeStringUrl  3 5 3                           System out println decodeStringUrl  Hello emoticons 2C 2C F0 9F 98 80 F0 9F 98 81 F0 9F 98 8A F0 9F 98 8B F0 9F 98 8E F0 9F 98 8A F0 9F 98 8D E2 98 BA F0 9F 98 98 E2 98 BA F0 9F 98 91 F0 9F 98 87 F0 9F 98 98 F0 9F 98 8B F0 9F 90 84        public static String encodeStringUrl String url        String encodedUrl  null      try            encodedUrl   URLEncoder encode url   UTF-8          catch  UnsupportedEncodingException e            return encodedUrl            return encodedUrl     public static String decodeStringUrl String encodedUrl        String decodedUrl  null      try            decodedUrl   URLDecoder decode encodedUrl   UTF-8          catch  UnsupportedEncodingException e            return decodedUrl            return decodedUrl

User · Answer

Step 1  change your database s default charset  ALTER DATABASE database name CHARACTER SET   utf8mb4 COLLATE   utf8mb4 unicode ci   if the db is not created yet  create it with correct encodings  CREATE DATABASE database name DEFAULT CHARSET   utf8mb4 DEFAULT COLLATE   utf8mb4 unicode ci   Step 2  set charset when creating table  CREATE TABLE IF NOT EXISTS table name         ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4 unicode ci   or alter table ALTER TABLE table name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci  ALTER TABLE table name MODIFY field name TEXT CHARSET utf8mb4

User · Answer

My answer only adds to Selvamani P answer   You might also need to change any SET NAMES utf8 queries with SET NAMES utf8mb4  That did the trick for me   Also  this is a great article to port your website from utf8 to utf8mb4  In particular the article makes 2 good points on indexes and repairing tables after converting them to utf8mb4   INDEXES     When converting from utf8 to utf8mb4  the maximum length of a column   or index key is unchanged in terms of bytes  Therefore  it is smaller   in terms of characters  because the maximum length of a character is   now four bytes instead of three        The InnoDB storage engine has a maximum index length of 767 bytes  so for utf8 or utf8mb4 columns  you can index a maximum of 255 or 191 characters  respectively  If you currently have utf8 columns with indexes longer than 191 characters  you will need to index a smaller number of characters when using utf8mb4    REPAIRING TABLES     After upgrading the MySQL server and making the necessary changes   explained above  make sure to repair and optimize all databases and   tables  I didn   t do this right away after upgrading  I didn   t think it   was necessary  as everything seemed to work fine at first glance   and   ran into some weird bugs where UPDATE statements didn   t have any   effect  even though no errors were thrown    Read more about the queries to repair tables on the article

User · Answer

For Rails  next to the accepted answer  don t forget to add  encoding  utf8mb4 collation  utf8mb4 bin  to your database yml

User · Answer

Hi my friends This is how I solved this problem and I was happy to teach it to you as well I am in the Android application I encrypt a string containing text and emoj and send it to the server and save it in the mysql table and after receiving it from the server I decrypt it and display it in the textview  encoded and decoded my message before request and after response  I send Android app messages to mysql via pdo through this method and receive them with pdo  And I have no problem  I think it was a good way  Please like Thankful   x000D   x000D     public void main          String message  hi mester ali moradi    how are you         String encoded message encodeStringUrl message       String decode message decodeStringUrl encoded message       public static String encodeStringUrl String message            String encodedUrl  null          try               encodedUrl   URLEncoder encode message   UTF-8              catch  UnsupportedEncodingException e                return encodedUrl                    return encodedUrl             public static String decodeStringUrl String message            String decodedUrl  null          try               decodedUrl   URLDecoder decode message   UTF-8              catch  UnsupportedEncodingException e                return decodedUrl                    return decodedUrl        x000D   x000D   x000D   message   hi mester ali moradi    how are you   encoded   ghgh F0 9F 98 AE F0 9F A4 90 F0 9F 98 A5 decoded  hi mester ali moradi    how are you

User · Answer

For anyone trying to solve this on a managed MySQL instance  in my case on AWS RDS   the easiest way was to modify the parameter group and set the server character set and collation to be utf8mb4 and utf8mb4 bin  respectively  After rebooting the server  a quick query verifies the settings for system databases and any newly created ones    SELECT   FROM information schema SCHEMATA S

User · Answer

1  Database  Change Database default collation as utf8mb4   2  Table  Change table collation as CHARACTER SET utf8mb4 COLLATE utf8mb4 bin                          Query    ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 bin   3  Code    INSERT INTO tablename  column1  column2  column3  column4  column5  column6  column7  VALUES   273    3    Hdhdhdhhzhzhzzhjzj         49  1   2016-09-13 08 02 29    2016-09-13 08 02 29     4  Set utf8mb4 in database connection       database connection   new mysqli  server   user   password   database name       database connection- gt set charset  utf8mb4

[mysql] How to store Emoji Character in MySQL Database

Examples related to mysql