How to setup Utf-8 for MySql on Ubuntu?

Question

I have the next table:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

I use the JDBC driver. Tables with text fields that must support utf8, is created like this:

jdbcTemplate.execute(
"CREATE TABLE IF NOT EXISTS users"+
"(id BIGINT NOT NULL AUTO_INCREMENT,"+
  "name VARCHAR(45),"+
  "................."+
  "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

On the local machine, everything works. But on the Ubuntu server, I'm getting "?????" instead normal name in the UTF-8. How to fix it? The application works via Nginx. Perhaps this is the problem?

Answer 1

Add entry to file /etc/mysql/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4

This worked for me. And I created database through the terminal:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This is unnecessary -> "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4" As a result, the worksheet looks like this:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)