5

I have a unique key on a column. When I insert a and then å it throws an error:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'å' for key 'constraint-1'' in ..

....But a and å are different UTF characters - so what's going on....?

table has DEFAULT CHARSET=utf8mb4 and utf8mb4_unicode_ci collation.

Grant
  • 2,413
  • 2
  • 30
  • 41
Dannyboy
  • 1,963
  • 3
  • 20
  • 37
  • MySQL applies a collation to each column which defines sort order and letter equivalence. For the collation applied to this column those two letters are considered equivalent. –  Jul 15 '15 at 20:19
  • @HoboSapiens I thought collation is only used for sorting, and the only thing that matters for insertion is a charset?? – Dannyboy Jul 15 '15 at 20:20
  • possible duplicate of [MySQl distinction between e and é (e acute) - index UNIQUE problem](http://stackoverflow.com/questions/6466901/mysql-distinction-between-e-and-%c3%a9-e-acute-index-unique-problem) –  Jul 15 '15 at 22:01
  • That's where you're wrong. See the question I linked to. You need to use the `utf8_bin` collation to distinguish these characters. You can override the default collation with the COLLATE clause. The MySQL reference on the subject is [here](https://dev.mysql.com/doc/refman/5.6/en/charset-collations.html) –  Jul 15 '15 at 22:08

1 Answers1

-4

use collation latin1_swedish_ci on the unique column.

This is my server screen

Screen after selecting database

Screen after selecting table

Records in the table

Error while entering duplicate value

Pratik Soni
  • 2,498
  • 16
  • 26
  • I wasn't the one who downvoted you, but I do store utf data - so I think simply changing collation doesnt work for this case. – Dannyboy Jul 15 '15 at 20:38
  • Yes i have tested in same environment and it worked for me. – Pratik Soni Jul 15 '15 at 20:42
  • my `Server connection collation = utf8mb4_unicode_ci`. Collation - table `latin1_swedish_ci`. `latin1_swedish_ci` works perfect for me. Can you please try once. – Pratik Soni Jul 15 '15 at 20:44
  • here is full detail. Server: 127.0.0.1 via TCP/IP Server type: MySQL Server version: 5.6.24 - MySQL Community Server (GPL) Protocol version: 10 User: root@localhost Server charset: UTF-8 Unicode (utf8) – Pratik Soni Jul 15 '15 at 20:47
  • 2
    Switching to a more restrictive collation like `latin1_swedish_ci` is daft when the rest of the world is moving to UTF8. Even if this works, it's a poor solution. –  Jul 15 '15 at 22:03