Add support for the utf8mb4_0900_ai_ci collation

[pento]

MySQL 8.0 adds the utf8mb4_0900_ai_ci collation, based on the current Unicode Collation algorithm. We should use it when possible.

Note that the version check will need to be a little more complex than the utf8mb4_520 check - MariaDB 10.x doesn't support utf8mb4_0900_ai_ci.

[pento]

This will probably need a method to detect server vendor - when we add this, the changes related to #33171 will probably need to be updated.

[markoheijnen]

Current check is already broken for MariaDB. I'm running version 10.1.18 which has support for utf8mb4_unicode_520_ci but shows up as 5.5.5-10.1.18-MariaDB-1~jessie. So ends up being version 5.5.5. So lower then 5.6.

I assume using a query like "SHOW COLLATION;" would be to slow?

[pento]

Ugh, that's annoying. Thanks for the info, @markoheijnen. I'll have to do some testing and see what we can come up with.

I'd like to avoid a SHOW COLLATIONS call if at all possible.

[sierkb]

Any progress in this issue?

If using MySQL 8.0, would an explicit
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', 'utf8mb4_0900_ai_ci');
in wp-config.php be usefull right now?

If using MySQL 5.7.x, what should be used instead?
What are the defaults in WordPress as of today?
define('DB_CHARSET', 'utf8'); ?
define('DB_COLLATE', 'utf8mb4_unicode_ci'); ?

[iisti]

I guess WordPress still doesn't support the MySQL 8 default utf8mb4_0900_ai_ci

MySQL Server version: 8.0.32 Source distribution

Defaults are when creating with command CREATE DATABASE testdb;

| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_0900_ai_ci     |
+----------------------------+------------------------+

So I guess one needs to create the database for WP with the command below in MySQL 8 to configure the character set and collate porperly.

CREATE DATABASE testdb02 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;