Why is my View utf8 and how can I change it to latin1

mysql view utf-8 character-encoding latin1
VenerableAgents picture VenerableAgents · Feb 23, 2012 · Viewed 8.4k times · Source

I'm having an issue where a client program needs to use a View, but it needs the results as latin1. Here's what I've got:

mysql> show global variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> Select CHARACTER_SET_CLIENT, COLLATION_CONNECTION FROM information_schema.VIEWS v;
+----------------------+----------------------+
| CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+----------------------+----------------------+
| utf8                 | utf8_general_ci      |
+----------------------+----------------------+

Answer

Mosty Mostacho picture Mosty Mostacho · Feb 27, 2012

Try running the following commands:

SET character_set_client = latin1;
SET character_set_results = latin1;
SET character_set_connection = latin1;
DROP VIEW your_view;
CREATE VIEW your_view as (
    here_goes_your_view_query);

Basically we're recreating the view. I've tested it locally and it worked, even after setting the DB, the table and the column to default to utf8

Related questions

Can I create view with parameter in MySQL?

I have a view like this: CREATE VIEW MyView AS SELECT Column FROM Table WHERE Value = 2; I'd like to make it more generic, it means to change 2 into a variable. I tried this: CREATE VIEW MyView AS SELECT Column FROM …

Read More
MySQL: View with Subquery in the FROM Clause Limitation

In MySQL 5.0 why does the following error occur when trying to create a view with a subquery in the FROM clause? ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause If this is a limitation of the MySQL …

Read More
Grant a user permission to only view a MySQL view and nothing else

This question was originally using MySQL 5.1.44, but is applicable to MySQL 8.0+ too. Let's say I have a table with records inserted by different users of my application. How can I give a specific user access to only see his/her …

Read More