如何使用mysqli设置排序规则? [英] How to set a collation with mysqli?

问题描述

我的数据库使用utf8_czech_ci排序规则，我也想将其设置为数据库连接.如果我不希望使用默认值utf8_general_ci，则Mysqli_set_charset将不允许我设置排序规则.建议首先在此处通过mysqli_set_charset设置字符集，然后通过设置名称.所以我做到了，连接排序规则仍然是utf8_general_ci.

My database uses utf8_czech_ci collation and I want to set it to my database connection too. Mysqli_set_charset won't let me set collation, if I don't happen to want the default one, utf8_general_ci. Here was suggested first to set the charset through mysqli_set_charset and then to set collation by SET NAMES. So I did it and connection collation is still utf8_general_ci.

现在我基本上使用代码 YourCommonSense 建议:

now I basically use the code YourCommonSense suggested:

  $spojeni=mysqli_connect('mysql01','username','password', 'my_database');
  if (!$spojeni) die('Nepodařilo se připojit k databázi.');
  mysqli_set_charset($spojeni,'utf8');
  mysqli_query($spojeni, "SET COLLATION_CONNECTION = 'utf8_czech_ci';");

但是，我只是遇到排序规则的非法组合错误.通过mysqli_get_charset()测试我的连接排序规则时，我发现我的排序规则是"utf8_general_ci"，而不是应该的"utf8_czech_ci".这意味着最初接受的答案由于某种神秘的原因而停止了工作.我在本地主机和托管服务器上都进行了测试，甚至在声明数据库后也进行了测试，因此偶然更改排序规则的错误似乎是不可能的.

However, I just faced Illegal mix of collations error. While testing my connection collation through mysqli_get_charset() I found that my collation is "utf8_general_ci", not 'utf8_czech_ci' as it should be. This means that the original accepted answer ceased to work for some mysterious reason. I tested it both on localhost and hosted server, and even just after declaring the database, so some error of changing the collation incidentally seems impossible.

那么如何通过其他方式更改排序规则?

So how to change the collation some other way?

我以前的代码，出于存档原因:

My former code, for archiving reasons:

  $spojeni=mysqli_connect('mysql01','username','password');
  if (!$spojeni) die('Nepodařilo se připojit k databázi.');
  mysqli_query($spojeni, "USE my_database");
  mysqli_set_charset($spojeni,'utf8');
  mysqli_query($spojeni, "SET NAMES 'utf8' COLLATE 'utf8_czech_ci';");

show variables like "%collation%";将collation_connection的值显示为utf8_general_ci；其他排序规则变量设置为utf8_czech_ci.在Adminer中运行此命令时，只有在SET NAMES或SET COLLATION_CONNECTION中的任何一个之后运行它时，才在collation_connection处看到utf8_czech_ci(有关确切语法，请参见上面代码的最后几行).当我从php代码(通过mysqli_query)运行show variables时，它显示为utf8_czech_ci，但是比较从表中选择的值和客户端设置的值仍然会抱怨

show variables like "%collation%";shows the value of collation_connection as utf8_general_ci; the other collation variables are set to utf8_czech_ci. When running this command in Adminer, I see utf8_czech_ci at collation_connection only when I run it after any of SET NAMES or SET COLLATION_CONNECTION (see last lines of my code above for exact syntax). When I run show variables from php code (through mysqli_query), it is shown to be utf8_czech_ci, but comparing a value selected from a table and a value set by the client still complains about illegal mix of collations.

我的相关问题中的详细信息.

Details details in my related question.

我发现错误的一种很好的解决方法.我仍然不明白为什么Adminer和Mysqli查询中显示的变量之间会有区别，但是我已经在 follow-问题.与我的怀疑不同，最初接受的答案至少在大多数情况下都有效，因此我再次接受.

I found a good workaround for the errors. I still don't understand why there's the difference between variables shown in Adminer and in Mysqli query, but I already asked for it in the follow-up question. Unlike my suspicion, the originally accepted answer works, at least most of the time, so I accept it again.

推荐答案

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli=mysqli_connect('mysql01','username','password', 'my_database');
mysqli->set_charset('utf8');
mysqli->query("SET collation_connection = utf8_czech_ci");

这篇关于如何使用mysqli设置排序规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！