COLLATION对于与我的设置不符的CHARACTER SET无效 [英] COLLATION is not valid for CHARACTER SET not corresponding to my settings

问题描述

我已经问另一个问题，因为我的排序规则设置问题似乎被某些不需要的默认值忽略/覆盖.要解决此问题，我想在查询中使用COLLATE，返回"排序规则的非法混合"错误.但是，当我尝试这样做时，却收到"COLLATION invalid"错误.

I have asked another question for my problem with collation setting, which seems to be ignored/overriden by some unwanted defaults. To workaround this, I want to use COLLATE in the queries returning "illegal mix of collations" error. However, when I try to, I get "COLLATION is not valid" error instead.

有问题的查询(我在其他查询中还没有遇到这个问题)，这次是在参数上使用COLLATE(?转换为一个用逗号分隔的字符串):

The query in question (I didn't face this problem with other queries yet), this time with COLLATE on the parameter (? translates to a string of comma-separated numbers):

SELECT k.url 
FROM kml k 
WHERE (
    SELECT GROUP_CONCAT(
        kat.kategorie ORDER BY kat.kategorie 
    )
    FROM kml_kategorie kat
    WHERE kat.kml = k.id
) = ? COLLATE utf8_czech_ci
LIMIT 1;

运行它时，出现以下错误:

When running it, I get following error:

COLLATION 'utf8_czech_ci' is not valid for CHARACTER SET 'binary'

在Adminer中运行show variables like "%character_set_%"时，得到以下结果:

When I run show variables like "%character_set_%" in Adminer, I get following results:

---------------------------------------  
| Variable_name            | Value    |  
---------------------------------------  
| character_set_client     | utf8     |  
| character_set_connection | utf8     |  
| character_set_database   | utf8     |  
| character_set_filesystem | binary   |  
| character_set_results    | utf8     |  
| character_set_server     | utf8     |  
| character_set_system     | utf8     |  
---------------------------------------

当我从php/mysqli调用命令时，唯一的区别是，即使character_set_filesystem的值也为utf8.

When I invoke the command from php/mysqli, the only difference is that even character_set_filesystem has value of utf8.

好吧，也许在这种情况下，无论对mysqli显示什么，都将使用数据库设置.但是后来我尝试通过将COLLATE添加到聚合函数GROUP_CONCAT中来解决任何强制性问题:

Well, perhaps in this case the database settings are used no matter what is shown to the mysqli. But then I tried to solve any coercibility issues by adding COLLATE to the aggregate function, GROUP_CONCAT:

SELECT k.url 
FROM kml k 
WHERE (
    SELECT GROUP_CONCAT(
        kat.kategorie ORDER BY kat.kategorie COLLATE utf8_czech_ci 
    )
    FROM kml_kategorie kat
    WHERE kat.kml = k.id
) = ?
LIMIT 1;

现在错误如下:

COLLATION 'utf8_czech_ci' is not valid for CHARACTER SET 'latin1'

您可以看到在字符集变量值中没有latin1.在修改my.cnf文件之前，它曾经是character_set_server的值.现在，其相关部分如下所示:

You can see that there is no latin1 among the charset variable values. It used to be the value of character_set_server before I modified my.cnf file. Its relevant part now looks like this:

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
collation_server=utf8_czech_ci
character_set_server=utf8
character-set-filesystem=utf8
#skip-character-set-client-handshake
#character_sets-dir="C:/xampp/mysql/share/charsets"

错误消息中显示的字符集(并导致问题)从何而来?我对从mysqli/php和adminer查询变量之间的差异感到困惑，而完全与其他变量混淆，使问题变得更加混乱.如何清除它并更改变量，以便至少此COLLATE解决方法有效?

Where do the charsets shown in the error messages (and causing the problem) come from? I'm bit confused by the differences between querying the variables from mysqli/php and adminer, and totally confused by some other variables making this even bigger mess. How to clean it and change the variables so that at least this this COLLATE workaround worked?

这基本上是我的链接问题的一个子问题，但差异很大，值得拆分；这两个问题加起来就像我的数据库变量一样混乱.但是，很可能其中一个解决方案也会使另一个解决.回答后，您可以考虑通过回答第一个答案的摘要来回答另一个问题，并注意它如何应用于问题中指定的问题.

This is basically a subquestion to my linked question, but different enough to merit splitting; the two questions combined would be as messy as my database variables. However, it's quite probable that the solution to one of them will make the other solved as well; after answering, you might consider answering the other question by a summary of your first answer + a note how it applies to the problem specified in the question.

对于这种特殊情况，我发现了一种解决方法.但是，我仍然没有找到为什么通过Adminer和Mysqli进行的查询返回了不同的结果，并且为什么错误消息指向的变量不包含任何字符集?

I found a workaround for this one particular case. However, I still didn't find why the queries made through Adminer and Mysqli returned different results and especially why the error message points to charsets not included in any variable?

推荐答案

kat.kategorie的数据类型是什么?

What is the datatype of kat.kategorie ?

GROUP_CONCAT( kat.kategorie
              ORDER BY CONVERT(kat.kategorie USING utf8) COLLATE utf8_czech_ci 
            )

您可能还需要致电CAST(... AS CHAR).

将来，请勿混合使用CHARACTER SET.

In the future, do not mix CHARACTER SETs.

GROUP_CONCAT以及涉及组合事物的其他几个函数在推断结果类型应该是什么方面存在问题.在某些情况下，它会下标并调用结果BINARY，它甚至比您遇到的还要糟糕.

GROUP_CONCAT, and a couple of other functions that involve combining things, have a problem in deducing what the result type should be. In some cases, it punts and calls the result BINARY, which is even worse than what you encountered.

我认为某个地方存在latin1会导致您遇到特定的问题.进入管理员.让我们看看SHOW CREATE TABLE KAT.

I believe that there is a latin1 somewhere that led to your particular problem. Dig into Adminer. Let's see SHOW CREATE TABLE kat.

这篇关于COLLATION对于与我的设置不符的CHARACTER SET无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！