Not able to insert CAFÉ in mysql, even after setting encoding correctly

Question

I inserted the word CAFE in name field mySQL table.

Unexpectedly, I get a row containing CAFE when I execute below statement

SELECT * FROM myTable where name='CAFÉ';

, which is wrong. In my use-case CAFE shouldn't be equal to CAFÉ

I think I set all the encodings correctly on server and client side:

Server side:

By modifying /etc/mysql/my.cnf I get below

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Client Side:

      connect = DriverManager
                        .getConnection("jdbc:mysql://"+serverName+"/" + 
 dataBaseName + "?characterEncoding=utf8&user=" + userName + "&password=" + password);

p.s. there are many duplicate questions similar to this, but none of them answering specific to what I am running into.

Long shot, but did you also try the solution described in this answer: http://stackoverflow.com/a/5629121/3080094 ? — vanOekel, Jan 13 '14 at 23:25
+1 Using `BINARY` after `WHERE` worked i.e. it was able to differentiate between CAFE and CAFÉ. Thank you. Now, I have to figure out how to insert CAFÉ in table without tripping unique constraint on name. — Watt, Jan 14 '14 at 00:00
You can make the column of VARBINARY type, but then you will have to be extra carefull with character sets (all clients need to use the same). As alternative, these answers might be interesting: http://stackoverflow.com/a/458544/3080094 http://stackoverflow.com/a/264389/3080094 — vanOekel, Jan 14 '14 at 00:53
Glad it worked out, I've posted an answer (after you tested the solutions, thanks for that). — vanOekel, Jan 14 '14 at 14:07

score 2 · Accepted Answer · edited May 23 '17 at 12:15

Collation utf8_general_ci (_ci stands for Case Insensitive) does not only make e equal to E, but also makes E equal to É. To make a select statement case sensitive, you can use the solution from this answer:

SELECT * FROM myTable where BINARY name='CAFÉ';

If you want to make data in column name always case sensitive, use a case sensitive _bin collation as shown in the answers for this question. E.g. when you create a table, use:

CREATE TABLE myTable (
...
) CHARACTER SET utf8 COLLATE utf8_bin ENGINE=MyISAM;

Watt · Answer 2 · 2014-01-14T01:29:13.083

0

Credit to @vanOekel comments

adding CHARACTER SET utf8 COLLATE utf8_bin while creating the table solved my problem

CREATE TABLE myTable (     
     name CHAR(100) NOT NULL,
     CONSTRAINT uc__name UNIQUE (name)     
) CHARACTER SET utf8 COLLATE utf8_bin ENGINE=MyISAM;

Now in my table I can have both CAFE and CAFÉ without tripping the unique constraint on name field.

edited Jan 14 '14 at 01:29

answered Jan 14 '14 at 01:21

Watt

3,118
14
54
85

Not able to insert CAFÉ in mysql, even after setting encoding correctly

2 Answers2