Mariadb – Collation conflicts between views and functions when using utf8mb4_unicode_ci collation

functionsmariadbview

Currently running MariaDB 10.3.24 and I really want to fully support the utf8mb4 unicode character set in my databases.

The problem I am running into is that when I try and set everything up to use the utf8mb4 character set and set all collation references to utf8mb4_unicode_ci I then have problems with views that have functions in them …. getting "collation mismatch" error on some but not all comparisons, e.g. this != that or this = that, etc. If I then set all the collation references back to utf8mb4_general_ci then all is fine ….. Is there something that I am doing wrong or is there some sort of work around that I have to do when creating views and functions when using the utf8mb4_unicode_ci collation ….

Thanks

Best Answer

Before defining Stored Routines, etc, establish the desired CHARACTER SET and COLLATION. One way to do that is via SET NAMES.

Also check

SHOW CREATE DATABASE

utf8mb4_unicode_520_ci is newer than utf8mb4_unicode_ci. (I don't know what collation changes were made by Unicode between their 4.0.0 and 5.2.0 versions. You may never notice a difference.)

Related Solutions

MySQL – Removing Per Column and Table Collation and Charset

Converting columns:

ALTER TABLE table_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8;

Converting tables:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

MySQL vs MariaDB – Differences in Character Set Handling

MySQL and MariaDB handle character sets the same. However... Different versions of MySQL have different defaults. Ditto for MariaDB.

SET NAMES declares what encoding is in the client. This is independent of the CHARACTER SET on a column or table. (There are, of course, characters that are not representable in latin1, so there can be conversion problems.) As you say, the conversion occurs on INSERT or SELECT.

To further pursue this, please tell us what client you are using, what connection parameters were used, any SETs performed, and what happened.

For diagnosing what is stored, use SELECT col, HEX(col).... More details.

Best Answer

Related Solutions

MySQL – Removing Per Column and Table Collation and Charset

MySQL vs MariaDB – Differences in Character Set Handling

Related Question