• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Which Character Set Should Be Used To Store Emojis in MySQL Database

by admin

As some emojis are using unicode code points beyond what can be represented with a three byte utf8 encoding, it is recommended to use utf8mb4 for columns that are used to store emojis. Alternatives are ucs16, ucs16le, and ucs32.

For example:

mysql> CREATE TABLE emoji (id int unsigned NOT NULL PRIMARY KEY, val varchar(12) NOT NULL) DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.57 sec)

-- Insert a value containing the emojis "high voltage sign" and "face screaming in fear":
mysql> INSERT INTO emoji VALUES (1, CONCAT(0xE29AA1, ' is scary ', 0xF09F98B1));
Query OK, 1 row affected (0.07 sec)

mysql> SET SESSION character_set_results = utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM emoji;
+----+-------------------+
| id | val               |
+----+-------------------+
|  1 | ⚡ is scary �� |
+----+-------------------+
1 row in set (0.00 sec)

The output above should be similar to the below screen shot if the terminal supports displaying the emojis:

character set MySQL for emojis

Be sure to test with the whole range of emojis being required for your application. Some emojis such as ⚡ can be stored in MySQL’s 3-byte utf8 encoding, but others such as face screaming in fear cannot.

Filed Under: mysql

Some more articles you might also be interested in …

  1. Which Ports are Used by mysqld, ndb_mgmd, and ndbd/ndbmtd in a MySQL Cluster Installation
  2. MySQL: how to figure out which session holds which table level or global read locks
  3. What are the various types of locking used in the MySQL Server
  4. How to change the audit log path in the MySQL Docker
  5. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  6. How to Restrict MySQL User Creation with Blank Password
  7. What are Reserved User Accounts in MySQL
  8. MySQL Shell: Using External Python Modules
  9. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  10. Beginners Guide to Implementing Table Maintenance in MySQL

You May Also Like

Primary Sidebar

Recent Posts

  • ncat Command Examples in Linux
  • ncat: command not found
  • nautilus Command Examples in Linux
  • namei: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright