MySQL无法与PHP的元音突变进行交互

Question

I'm pretty desperate on this one.

Thing is I'm from Germany and we got vowel mutations (ä,ü,ö. etc). I got a location database and want to make some queries to it. Hence it all worked fine and when I was trying to find "München" (you might know it as Munich) it broke. In general I'm using the TableGateways provided by ZF2, but they are not the problem (check the list below).

Basically it always screws up on vowel mutations. What I tried and checked:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8" (no effect)

doublechecking the database collation ( utf8 general_ci)

checked my file_encoding (utf8)

playing arround with iconv and utf8_de/encode (no effect)

Logging the queries which flew by

 140428 11:59:49      394 Query SET PROFILING=1
  394 Query SHOW STATUS
  394 Query SHOW STATUS
  394 Query SELECT `location`.* FROM `location` WHERE `city` LIKE 'münc%'
  394 Query SHOW STATUS

Trying the query on navicat and by terminal (worked)

and writing a quick and dirty mysql_connect(); mysql_query()- Test which also failed.

Edited the mysql config to use utf over latin1 as default charset.

And now I don't have the slightest clue what else I can do to fix it.

Answer 1

Try to use utf8_unicode_ci collation instead of utf8_general_ci. General_ci removes vowels and other special characters and translate all of them to "A" and "U", for example:

ÀÁÅåāă = A
ü = U

utf8_unicode_ci slower, but works with this characters as you expect.

UPDATE:

sql:

CREATE TABLE `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `location` VALUES (1,'München');

script:

$dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'xxxxx',array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$q = $dbh->prepare("SELECT * FROM `location` WHERE `city` LIKE 'münc%'");
$q->execute();
print_r($q->fetch());

And executing:

$ php catalog_mdmtointra.php                                                                                                                                                                                                            
Array                                                                                                                                                                                                                                                                          
(                                                                                                                                                                                                                                                                              
    [id] => 1                                                                                                                                                                                                                                                                  
    [0] => 1                                                                                                                                                                                                                                                                   
    [city] => München                                                                                                                                                                                                                                                          
    [1] => München                                                                                                                                                                                                                                                             
)            

UPDATE2:

My versions:

ii  percona-server-common-5.5            5.5.33-rel31.1-566.wheezy          i386         Percona Server database common files (e.g. /etc/mysql/my.cnf)
ii  php5-mysqlnd                         5.4.4-14+deb7u2                    i386         MySQL module for php5 (Native Driver)