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/ |
+--------------------------+----------------------------+

Konstantin Yovkov · Accepted Answer · 2014-01-13 13:20:05Z

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");

edited Jan 13, 2014 at 13:20

answered Jan 13, 2014 at 13:07

Konstantin Yovkov

63k9 gold badges107 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yeah its me Over a year ago

changed all that but still I'am getting that bad encoded string

Konstantin Yovkov Over a year ago

Did you restart the MySQL server ?

yeah its me Over a year ago

also that happens only when I insert it from java code, if I insert a string from phpmyadmin for example, its all okay, I've noticed that it only happens with cyrillic letters, but why that happens if the encoding is utf-8

wangdq · Accepted Answer · 2014-01-13 13:21:58Z

0

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.

answered Jan 13, 2014 at 13:21

wangdq

2,00418 silver badges26 bronze badges

4 Comments

wangdq Over a year ago

try restart your mysql service.

yeah its me Over a year ago

mysqld restart, nothing changed

wangdq Over a year ago

I suggest 1)create a temporary database and table using utf8,do insert test(use SQL sentence),see what happen. 2)my jdbc like [jdbc:mysql://localhost:3306/mapping?characterSetResults=UTF-8&characterEncoding=UTF-8&useUnicode=yes] work,check your url again.

yeah its me Over a year ago

the database and tables are fully utf-8, I've added to the url characterSetResults=UTF-8, but that doesn't helped, I've did all what I've known...

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related