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;
