Mysql: Set collation for view not working

Question

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/

What charset and collation were established for the DATABASE you are in? And of the connection? — Rick James
@RickJames currently when I try to set collation as latin1_swedish_ci, it throws error as "COLLATION latin1_swedish_ci is not valid for CHARACTER SET utf8mb4" . The utf8mb4 is not present in any variables mentioned above, not sure how it came and how to solve this — Jake

Akina Akina · Accepted Answer · 2021-01-12T11:41:08

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

Mysql: Set collation for view not working

1 Answers