I am using PHP 8.1 and MySQL 8.0. All databases, tables and individual fields are all set to a collation of utf8mb4_0900_ai_ci
– except for databases: information_schema and performance_schema, which are utf8mb3_general_ci
(system default?).
Here are the global variables MySQL configs for collation and charset:
//performance_schema.global_variables
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 utf8mb3
collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb4_0900_ai_ci
collation_server utf8mb4_0900_ai_ci
default_collation_for_utf8mb4 utf8mb4_0900_ai_ci
As you can see, I am trying to set my server up to have a charset of utf8mb4
and a collation of utf8mb4_0900_ai_ci
(which is what MySQL 8.0 recommends)
performance_schema.session_variables has a slight different set of variables, I’m not sure if this makes a difference or not. I’ve tried rebooting MySQL, but the session variables do not update to what the global variables are set to above. I’ve tried manually changing these, but they won’t change, even if I’m using the MySQL admin user:
//performance_schema.session_variables
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb3
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8mb3
collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb3_general_ci
collation_server utf8mb4_0900_ai_ci
default_collation_for_utf8mb4 utf8mb4_0900_ai_ci
Here is my script to connect to MySQL and print the charset data:
$conn = mysqli_connect($hostname, $username, $password, $database);
mysqli_set_charset($conn,"utf8mb4");
mysqli_query($conn, "SET collation_connection = 'utf8mb4_0900_ai_ci'");
mysqli_query($conn, "SET SESSION collation_connection = 'utf8mb4_0900_ai_ci'");
var_dump(mysqli_get_charset($conn));
//
object(stdClass)#2 (8) {
["charset"]=> string(7) "utf8mb4"
["collation"]=> string(18) "utf8mb4_general_ci"
["dir"]=> string(0) ""
["min_length"]=> int(1)
["max_length"]=> int(4)
["number"]=> int(45)
["state"]=> int(1)
["comment"]=> string(13) "UTF-8 Unicode"
}
The problem is that "collation" is showing utf8mb4_general_ci
, but it’s supposed to be utf8mb4_0900_ai_ci
. I’ve read through various guides here and I am unable to fix this. Anyone know how to do this properly?
2
Answers
You have set the collation correctly. The information that you see in
mysqli_get_charset
is irrelevant.Try:
and you will see the right collation.
I am not sure, but I think mysqlnd has no way of figuring out which collation is actually in use. You can only set charset, but you cannot set the collation, so it will always report the default collation. But it doesn’t matter much for mysqlnd as collation is not used by PHP. You can safely set it like you are doing it now.
Collation belongs to the database.
ALTER DATABASE
database
/*!40100 DEFAULT COLLATE latin2_hungarian_ci */;works for me.
you can find collations here:
https://mariadb.com/kb/en/setting-character-sets-and-collations/