我遇到了一个问题，在使用 PHP 与命令行进行查询时，我得到的结果排序不同。根据我的研究，在某些情况下，糟糕的编码似乎会导致结果的顺序出现问题。

也就是说，我所有的数据库表都编码为 utf8mb4，排序规则为 utf8mb4_general_ci。但是，似乎 mysql 变量设置不正确。

我在 Mysql 5.5.5-10.1.26-MariaDb 上。

这是我的 CNF 设置，但老实说我不知道​​我在这里做什么:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mariadb]


[mysqld]

character-set-server=utf8mb4
character_set_client=utf8mb4
collation-server=utf8mb4_general_ci

mysql输出的变量:

character_set_client        utf8
character_set_connection    utf8
character_set_database      utf8mb4
character_set_filesystem    binary
character_set_results       utf8
character_set_server        utf8mb4
character_set_system        utf8
collation_connection        utf8_general_ci
collation_database          utf8mb4_unicode_ci
collation_server            utf8mb4_general_ci

更新:有人问我如何连接到数据库:

$this->connection = new PDO('mysql:host='.DB_SERVER.';dbname='.DB_NAME.';port='.DB_PORT, DB_USER, DB_PASS, $options);

更新:我已切换到 utf8mb4_unicode_ci(根据以下答案中的建议)。

最佳答案

您还想拥有 character-set-client-handshake = FALSE。

用/etc/my.cnf.d/character-set.cnf

# https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/
# https://mariadb.com/kb/en/library/setting-character-sets-and-collations/
# https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434
# https://stackoverflow.com/questions/47566730/force-mariadb-clients-to-use-utf8mb4

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server = utf8mb4

我得到的一切都是 utf8mb4¹

MariaDB [(none)]> show variables like 'char%'; show variables like 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

然而，如果没有字符集客户端握手行，一些仍然是 utf8

MariaDB [(none)]> show variables like 'char%'; show variables like 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)

MariaDB [(none)]>

¹ character_set_system 是 always utf8 .

关于mysql - 强制 MariaDB 客户端使用 utf8mb4，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/47566730/