In MySQL, what do I put inside my.cnf so that all tables are UTF-8 that works with emojis by default?

Question

I'd like every table and database (to be created) to be utf-8 that works with emojis. I understand that there are a few variables I need to define inside my.cnf:

init_connect='SET collation_connection = ??? '
init_connect='SET NAMES ???'
character-set-server = ???
collation-server = ???

However, I'm not sure what to put in the ???. What do I put inside my.cnf?

Answer 1

This article may help: https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4

It explains in detail how to switch to utf8mb4 to support full unicode, thus allowing emojis using the following config:

[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

Answer 2

In addition to changing to utf8mb4 as @eol suggests, there may be more to do.

If you already have tables that have utf8 columns; do ALTER TABLE .. CONVERT TO CHARACTER SET utf8mb4.

When you connect, be sure to establish UTF-8 if the client has a non-mysql way of doing it, or use SET NAMES utf8mb4.

Keep in mind that root ignores init-connect. You should have a non-root 'user' for the application.

Answer 3

To support full Unicode in MySQL databases

1. For database: ALTER DATABASE DATABASE_NAME CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

2. For table: ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. For column: ALTER TABLE TABLE_NAME CHANGE COLUMN_NAME COLUMN_NAME VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;