Search code examples
mysqldatabasecollationcharacter-set

Mysql: Set collation for view not working


I want to set the collation of a view to default database. But the option is not available. Even if I try to set anything other than that, the same is not reflected at columns as individually they have different collation.

Code:

create or replace view my_view as
select * from table_a a
inner join table_b b
on a.id = b.fk_id
collate utf8mb4_unicode_ci

But if I query

SHOW FULL COLUMNS FROM my_view

the result set contains collation as utf8mb4_general_ci

Can anyone explain why this is happening? And how do I correct it?

More Info

SHOW VARIABLES LIKE "char%";
variable_name value
character_set_client utf8
character_set_connection utf8
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/local/mysql/share/charsets/

Solution

  • Your collate utf8mb4_unicode_ci affects on joining condition only.

    If you need to alter resultset collation then you must use separate columns list instead of asterisk with separate collation change (CONVERT function) or specifying for each string-type column.

    DEMO