在对mySQL数据库中的值进行排序时,我得到了以下错误的排序顺序:
SELECT * FROM tt_news WHERE pid=19 AND deleted=0 AND hidden=0 order by title ASC
A
B
C
...
Ä
Ö
如何解决这个排序问题?应在A之间或之后,依此类推。
MySQL服务器版本:5.0.51a,支持UTF-8
我发现这与数据库的整理有关(见德语链接:http://mysql-faq.sourceforge.net/tables3.html)。
脚本嵌入到TYPO3中,setDBinit
设置为SET NAMES utf8
,forceCharset
设置为UTF-8
。因此UTF-8数据应该存储在ISO-8859-1(拉丁文1)中。
该列的类型为text
,排序规则为latin1_swedish_ci
。当我在phpMyAdmin中输入SHOW VARIABLES LIKE 'collation%'
时,我得到
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
SHOW VARIABLES LIKE '%CHARACTER_SET%';
在phpMyAdmin 中给我
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
第1次尝试:
我试图在脚本中使用SET NAMES utf8;
,但这并没有改变什么。
第2次尝试:
我想在PHP中进行排序(根据这个SOQ:如何在PHP中根据给定键的值对关联数组数组进行排序?),但这并没有改变排序。
$title=array();
foreach ($result as $key => $row) {
$title[$key] = $row['title'];
}
array_multisort($title, SORT_ASC, $result);
第3次尝试:
我使用了以下mySQL语句(取自http://blog.mixable.de/mysql-order-by-und-deutsche-umlaute/):
SELECT * FROM tt_news WHERE pid=19 AND deleted=0 AND hidden=0 order by title COLLATE latin1_swedish_ci;
排序没有更改。使用utf-8
会导致错误(不允许排序规则)。
第4次尝试:
SELECT *, REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(REPLACE(title, 'Ä', 'A'), 'Ö', 'O'), 'Ü', 'U'), 'ä', 'a'), 'ö', 'o'), 'ü','u'), 'ß', 's') AS sortiert FROM tt_news WHERE pid=19 AND deleted=0 AND hidden=0 ORDER BY sortiert
来源:http://www.php-faq.de/q-mysql-umlaute-sortieren.html
可以在phpMyAdmin中使用,但不能在我的脚本中使用。我得到以下错误:Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'replace'
我可以在不更改字符集或排序规则的情况下用PHP进行正确的排序吗?
根据瑞典规则,您看到的排序是正确的:Å,É和Ö是字母表中Z之后的最后三个字母。如果你不喜欢,请更改列排序规则,换个别的。
alter table tt_news modify title text collate latin1_general_ci;
general
变体认为一个字符的所有重音变体都是不同的,但在排序时会将它们分组在一起;例如CCD_ 14在CCD_。如果你需要一些瑞典以外的国家标准,这里列出了MySQL开箱即用支持的内容:http://dev.mysql.com/doc/refman/5.6/en/charset-we-sets.html
如果您不能更改数据库中的列排序规则,您可以告诉MySQL只使用特定的排序规则来排序查询。例如:
.... order by title collate latin1_general_ci
纯PHP解决方案:
function sortWUmlauts($s1, $s2)
{
$s1 = $s1['title'];
$s2 = $s2['title'];
$search = array('Ä','Ö','Ü','ß');
$replace = array('A','O','U','s');
return strcasecmp(
str_ireplace($search, $replace, $s1),
str_ireplace($search, $replace, $s2)
);
}
// call
uasort($result, 'sortWUmlauts');
取自http://at2.php.net/manual/en/function.uasort.php#99017
一个很好的补充是有一个变量来保存关联数组的搜索关键字(直接将函数嵌入uasort调用中)。
对使用"按标题排序latin1_german1_ci"
Ä = A
Ö = O
Ü = U
ß = s
使用"按标题排序latin1_german2_ci"
Ä = AE
Ö = OE
Ü = UE
ß = ss
排序以获取更多http://dev.mysql.com/doc/refman/5.6/en/charset-we-sets.html
您不必修改数据库即可完成此操作(当然,除非您愿意)。也许您有不同的列,希望根据不同的语言进行排序?
只需在查询中指定不同的排序规则,例如:
SELECT * FROM tt_news WHERE pid=19 ORDER BY title COLLATE "utf8_german2_ci" ASC
请注意,如果您的表还没有在utf8排序规则中(也许它在latin1排序规则中),那么您将需要使用latin1排列规则进行排序。在这种情况下,您将在上面的查询中使用latin1_german2_ci
而不是utf8_german2_ci
。
这里的MySQL参考文档中提供了一个排序规则列表以及对其使用的有用讨论。