Why java strings are not saved as UTF-8 in MYSQL?

Question

message = new String(("round " + id).getBytes("UTF-8"));

conn = DriverManager.getConnection("jdbc:mysql://" + host + "/" + db + "?useUnicode=true&characterEncoding=UTF-8&"
              + "user=" + login + "&password=" + password);

When I make an insert into the database which encoding is UTF-8 CI, get something like this �������������������� 179, the java file encoding is utf-8, what I'am doing wrong?

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Answer 1

Generally, MySQL comes with a list of predefined system variables. If you want to list them, you can open the MySQL prompt and type:

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | latin1                     |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

As shown, MySQL's default encoding is latin1. In order to change it, you need to edit a bit your my.cnf file and add the following lines:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

When you configure the my.cnf file and restart the MySQL server, you'll note the difference.

Edit: You can set the encoding for the JDBC's DriverManager like this:

DriverManager.getConnection("jdbc:mysql://" + host + "/" + db + "?useUnicode=true&"
              + "user=" + login + "&password=" + password + "&characterEncoding=utf8");

Answer 2

Besides set connection string in java jdbc,An alternative way to change mysql charset:

1) specify utf8 when creating database and table

    //database
    CREATE DATABASE IF NOT EXISTS databasename 
    default charset utf8
    COLLATE utf8_general_ci;

    //table
    CREATE TABLE  `Extenics`.`BE_headset` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

2) or alter it after creating(I don't encourage this)

ALTER DATABASE db_name

    [[DEFAULT] CHARACTER SET charset_name]

    [[DEFAULT] COLLATE collation_name]

see mysql for more help.