MySQL Collations

It is important that correct collations be used in the MySQL database schema so that text comparisons work correctly and without error.

The chameleon/superticker database uses utf8mb4_unicode_ci as the default collation for all tables and columns.

If there is ever a mismatch when doing comparisons - this can result in errors.

Find non-default collations

To find tables with non-default collations use the following query:

-- find tables with non-standard collations
SELECT 
    table_name,
    table_collation
FROM information_schema.tables
WHERE table_schema = 'superticker'
  AND table_collation is not null and table_collation != 'utf8mb4_unicode_ci';

To find columns with non-default collations use the following query:

-- find columns with non-standard collations
SELECT 
   table_name,
   column_name, 
   character_set_name, 
   collation_name 
FROM information_schema.columns c
WHERE table_schema = 'superticker'
and collation_name is not null and collation_name != 'utf8mb4_unicode_ci';

Use cases that deviate

There can be use cases where the collation is changed for a client to support sorting and searching with their language specifics. This was done for a client in Denmark to improve the sorting in the Election Results.

ALTER TABLE elc_event CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci ;
ALTER TABLE elc_office CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci ;
ALTER TABLE elc_party CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci ;
ALTER TABLE elc_candidate CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci ;
ALTER TABLE elc_area CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci ;
ALTER TABLE story_topic CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci ;
ALTER TABLE story_playlist CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci ;