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/ |
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.