Change IS_DEFAULT value for INFORMATION_SCHEMA.COLLATIONS

Is it possible to alter the IS_DEFAULT value in the INFORMATION_SCHEMA.COLLATIONS table.

The reason why I am asking this is, that there are some (PHP-)Applications are using "SET names utf-8" as an initialization query without the possibility to provide the collation that should be used.

What happens in the SQL Client when you execute this query:

SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
+----------------------+-----------------+

SET NAMES utf8;

SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
+----------------------+-----------------+

The collation_connection falls back to the default that was defined in INFORMATION_SCHEMA.COLLATIONS.IS_DEFAULT. It would be great if I could change that to utf8_unicode_ci (or something else).

Answer Answered by Alexander Barkov in this comment.

There is no a way to change the default collation for a character set. Default collations are hard-coded. For example, utf8_general_ci is the hard-coded default collation for the character set utf8.

This is a missing feature.

The SQL standard has "CREATE CHARACTER SET" statement for this: https://kb.askmonty.org/en/create-character-set-statement/

So one can do:

CREATE CHARACTER SET utf8my AS GET utf8 COLLATE utf8_unicode_ci;

and then use this:

SET NAMES utf8my;

which effectively sets the connection character set to utf8, and the collation to utf8_unicode_ci.

MariaDB does not support CREATE CHARACTER SET yet.

The only workaround is to make the application send:

SET NAMES utf8 COLLATE utf8_unicode_ci;

instead of just:

SET NAMES utf8;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.