1

Possible Duplicate:
Looking for case insensitive MySQL collation where “a” != “ä”

I'm struggling with this utf8 nonsense, I create a test table:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

I insert a single row:

INSERT INTO `test`(`name`) VALUES ('Cryptïc');

I query against the table:

SELECT `name` FROM `test` WHERE `name` LIKE 'Cryptic';

I get result set:

+---------+
| name    |
+---------+
| Cryptïc |
+---------+

i should not equal ï, a little help?

Community
  • 1
  • 1
user17753
  • 3,083
  • 9
  • 35
  • 73
  • or [I get dual results from mysql query when using international charachters, i.e Å/Ä=A & Ö=O,](http://stackoverflow.com/q/4018950) – Pekka Oct 18 '12 at 20:00

1 Answers1

1

Use utf8_bin instead of utf8_general_ci.

With utf8_general_ci, similar characters (like i and ï) are treated as the same character in comparisons and sorting. The comparison is also case insensitive (hence the _ci), which means that i and I are also treated the same.

Other collations, like utf8_unicode_ci do better sorting, but still 'fail' on comparisons.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210