MySQL treats two different strings same

Question

MySQL throws an error on INSERT QUERY when two different strings jardiniere and jardinière are inserted in a column (named word) having UNIQUE KEY enabled.

Error: #1062 - Duplicate entry 'jardinière' for key 'word'

I've also included following line before INSERT Query (in PHP File)

mysqli_set_charset($con, "utf8");

How can I resolve this error?

Only utf8_bin collation would consider them to be different as they are semantically the same word. — Alex K., Apr 29 '19 at 12:13
@AlexK. Sort of beat me to the point, but you would need it to be `utf8_bin` instead of `latin1_swedish_ci`. — Qirel, Apr 29 '19 at 12:17

score 2 · Accepted Answer · answered Apr 29 '19 at 12:23

The collation of the table (the specific column) needs to be set to utf8_bin in order to separate the two characters from one another.

Run the following query to update the charset and the collation (you can set it to utf8_bin if you need it on just that column).

ALTER TABLE myTable
CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

Keep in mind that unlike latin_* and utf8_XXX_ci, the utf8_bin is case sensitive when comparing.

MySQL treats two different strings same

1 Answers1