Search code examples
sqlmariadb

MariaDB: Change the collation of a table containing a JSON column

In MariaDB the type JSON is an alias for LONGTEXT COLLATE utf8mb4_bin.

When I change the collation of a table containing a JSON column to utf8mb4_unicode_520_ci via

ALTER TABLE table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

will that change the collation of the JSON column as well?

If so: Does that pose a problem for the usability of said JSON column?

Solution

  • will that change the collation of the JSON column as well?

    Yes it will.

    If so: Does that pose a problem for the usability of said JSON column?

    Some problems that I can think off:

    • The collation change itself does not affect whether the JSON data remains valid, but impacts how string comparisons within JSON queries work. Queries that rely on case-sensitive or binary comparisons might produce different results after the collation change.

    • Using utf8mb4_unicode_520_ci, comparisons become case-insensitive which might differ from the behavior of utf8mb4_bin where comparisons are case-sensitive.

    • Using utf8mb4_unicode_520_ci might impact performance, especially for complex queries with string operations.

    My suggestion.

    Revert json column back to LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

    ALTER TABLE myTable 
  MODIFY COLUMN json_data LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

    See example