PHPMyAdmin强制使用ut8mb4作为默认排序规则 [英] PHPMyAdmin forces to use ut8mb4 as default collation

问题描述

好的，所以我花了整个上午试图更改XAMPP设置上的默认排序规则.

Ok, so I've spent the morning trying to change the default collation on my XAMPP setup.

问题出在这里:我在视图中使用Format()来将双精度型转换为字符串

Here's the problem: I'm using Format() in a view, to convert a double into a string

    CREATE VIEW `test` AS
    SELECT 
        Format(some_data_table.double_number,0) AS string_result
    FROM some_data_table;

当我查看返回的列时，其显示为utf8mb4_general_ci. 我已经尝试过my.ini和phpMyAdmin的config.inc.php中的所有设置方式 无济于事.

When I look at the returned column, its showing as utf8mb4_general_ci. I've tried all manner of settings in my.ini and phpMyAdmin's config.inc.php to no avail.

作为最后的选择，我准备将collat​​ion参数添加到视图中.

As a last resort, I'm prepared to add the collation parameter to view.

对于任何经过测试的解决方案，我将不胜感激

I'd be grateful for any tested solution

推荐答案

好吧-我将为落在这里的其他任何人发布我自己的答案: (我曾在其他地方看到过此消息，但由于没有任何解释，所以不信任它.)

Ok - i'm going to post my own answer for anyone else who lands here: (i had seen this somewhere else, but didn't trust it a t the time because there was no explanation).

当SQL Format()将数字转换为字符串时，它将使用变量 character_set_results . PMA的变量"标签将其显示为"utf8"，但随后在下面的一行上说(会话值)= utf8mb4.
因此，我知道PMA会覆盖服务器默认设置. 我真正的问题是我找不到改变此覆盖的方法-通过使用[mysqld] skip-character-set-client-handshake设置..或通过编辑php.config.inc文件. 今天，我取得了突破..我确定，如果我使用相同的PMA连接到较旧的MySQL服务器，则不会发生此问题. 这表明PMA在新的(支持)服务器上强制使用utf8mb4，而在较旧的服务器上则不使用. 我对phpmyadmin进行了文本搜索以查找字符串"mb4"，并在该类中找到了以下代码: phpMyAdmin/libraries/DatabaseInterface.class.php

When the SQL Format() turns a number into a string, it uses the variable character_set_results. PMA's Variables Tab was showing this as "utf8" but then on a line below, it was saying (session value) = utf8mb4.
So i was aware that PMA was overriding the server default. My real problem was that I could find no way to change this override - either by using the [mysqld] skip-character-set-client-handshake setting.. or by editing the php.config.inc file. Today I had a breakthrough.. I established that if I used the same PMA to connect to and older MySQL server, the problem did not occur. This suggested to be that PMA was forcing utf8mb4 on newer (capable) servers, but not older ones. I did a text search of phpmyadmin for the string 'mb4' and found the following code in the class: phpMyAdmin/libraries/DatabaseInterface.class.php

        // Skip charsets for Drizzle
    if (!PMA_DRIZZLE) {
        if (PMA_MYSQL_INT_VERSION >  50503) {
            $default_charset = 'utf8mb4';
            $default_collation = 'utf8mb4_general_ci';
        } else {
            $default_charset = 'utf8';
            $default_collation = 'utf8_general_ci';
        }

PMA_MYSQL_INT_VERSION> 50503 似乎与我有关较早的mysql版本的理论相符，因此我备份了文件并编辑了将 utf8mb4 替换为的类. utf8 ). phpMyAdmin现在在其变量选项卡中显示我想要的内容，而Format()函数现在返回我想要的内容.

the PMA_MYSQL_INT_VERSION > 50503 seems to fit with my theory about older mysql versions, so i've backed up the file and edited the class replacing utf8mb4 with utf8 in this function. phpMyAdmin is now showing what i want in its variables tab, and the Format() function is now returning what i expect.

这篇关于PHPMyAdmin强制使用ut8mb4作为默认排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！