MySql change collation in view

Question

I have a view "name_all" in mysql which structure is

colName     varchar(30)  utf8_general_ci

date    varchar(76)     utf8_general_ci

name  varchar(43)   latin1_swedish_ci

when I try to run query:

SELECT CONCAT(`colname`,' of Mr. ',`name`,' Expire on ',`date`) FROM name_all

it give error:

 #1271 - Illegal mix of collations for operation 'concat'

"colName" is basically a Column Name which is used as a row in this View

"date" is mysql Date which is converted in this format '%a %D %b, %Y'

"name" is concatenation of firstname, middlename, lastname from a table

How do I solve this issue and what is my mistake?

when i run query SHOW VARIABLES LIKE 'collation%' Result is

  Variable_name         Value
  collation_connection  utf8_general_ci
  collation_database    latin1_swedish_ci
  collation_server      latin1_swedish_ci

score 8 · Answer 1 · answered Jan 04 '17 at 03:41

8

For anyone else coming across this thread, the collation on the view column is determined by the underlying table columns UNLESS the column is a hard-coded string defined in the view (e.g. a CASE defining some string value). In these cases the collation is determined by the character set specified during view creation. During view definition export clients like HeidiSQL may include executable comments that set the character set to a default value other than your intended set. When you rerun the definition you've now saved your mismatched sets. Run "show full columns from viewName" to check.

answered Jan 04 '17 at 03:41

Carth

2,303
1
17
26

1

Thanks, this saved me some time. So essentially run `SET collation_server = 'utf8_unicode_ci'` before creating the view? – Jairus Jun 22 '20 at 02:43
4

@JairusMartin - That's correct. Whatever that set collation is will be the one used by the columns that are defining explicit string values rather than retrieving the values from an underlying table. – Carth Jun 22 '20 at 23:57
1

For anyone else coming upon this thread too, you can specify the collation of said hard coded string by using CONVERT(hard_coded_string USING charset) where charset is utf8 for example. This question has good examples, https://stackoverflow.com/questions/16051369/convert-output-of-mysql-query-to-utf8 – gordon_freeman May 14 '22 at 15:36
I had a problem with a view having a calculated field with a CASE statement; MySQL (8.0.1) created it with *utf8mb4_0900_ai_ci* despite the database's default collation being set to *utf8mb4_general_ci* leading to Illegal mix of collations error. Executing `SET collation_connection = 'utf8mb4_general_ci'` before creating the view fixed the issue. – dregad Dec 31 '22 at 12:53

score 1 · Accepted Answer · edited Jun 06 '12 at 05:38

1

Even though you say "all the tables and column are in latin1_swedish_ci", what yo u posted is:

colName   varchar(30)   utf8_general_ci

date   varchar(76)   utf8_general_ci

name   varchar(43)   latin1_swedish_ci

Changing colName and date collation to latin1_swedish_ci will probably sort out your problem.

edited Jun 06 '12 at 05:38

Abdel Raoof Olakara

19,223
11
88
133

answered Jun 06 '12 at 05:34

Shiham

2,114
1
27
36

but how can i do it in "views" – Shujaatali Jun 06 '12 at 05:35
1

Good question. You got to do this in the table of your database, View is simply like a looking at the data table with some defined set of rules [just as the name imply "VIEW"]. So view does not change any of the underlying design of your schema. Try changing collotion in database level. – Shiham Jun 06 '12 at 06:07

MySql change collation in view

2 Answers2