使用国际字符(即Å/Ä= A& Ö= O， [英] I get dual results from mysql query when using international characters, i.e Å/Ä=A & Ö=O,

问题描述

例如，如果我搜索名称Åsa，我只想获得名称Åsa而不是Asa，与Björn而不是Bjorn相同

For example if I search for the name Åsa i only want to get the name Åsa and not Asa, same with Björn instead of Bjorn

$query="select * from users where username like 'Björn'";
$result=mysql_query($query);
$num=mysql_num_rows($result);
echo"$num";
$i=0;
while($i<$num){     
$id=mysql_result($result,$i,"id");
$name=mysql_result($result,$i,"username");    
echo"<br/>$id,$name";
$i++;
}

结果

34,Björn
67,Bjorn

应该仅显示结果34
我正在使用

only result 34 is supposed to be displayed
I am using

mysql_query("SET NAMES utf8");
mysql_query( "SET CHARACTER SET utf8");

数据库，表和列设置为utf8_unicode_ci

The database, table and column are set to utf8_unicode_ci

推荐答案

您的问题"是utf8_unicode_ci归类.该排序规则会进行字符扩展"，这意味着，即使在=比较 中，Umlauts及其基本字符也被视为相同的:

Your "problem" is the utf8_unicode_ci collation. That collation does "character expansions", meaning that Umlauts and their base characters are treated as the same even in a = comparison:

A = Ä
O = Ö
...

此mySQL手册页上的第二个示例解释了该问题:

The second example on this mySQL manual page explains the issue: 9.1.7.8. Examples of the Effect of Collation

您要做的是要么切换到区分变音符号和基本字符的排序规则(例如utf8_general_ci或utf8_general_bin)，要么仅在进行比较时才切换到其他排序规则:

What you would have to do is either switch to a collation that distinguishes between umlaut and base character (e.g. utf8_general_ci or utf8_general_bin) or switch to a different collation only when doing the comparison:

select * from users where username like 'Björn' COLLATE utf8_general_ci;

这显然要慢一些，因为在查询过程中必须对每个记录进行排序规则转换.

this is obviously slower, because the collation conversion has to be done for each record during the query.

这篇关于使用国际字符(即Å/Ä= A& Ö= O，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！