Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    karachi , Pakistan

    Question Unanswered: MySql change collation in view

    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

  2. #2
    Join Date
    Jun 2012
    Denver, CO, USA
    I think the problem is that colName/date and name have different collations. You can cast one character set to another as follows:
    CAST(_latin1'test' AS CHAR CHARACTER SET utf8)
    Juacala - Web Application Developer, Eliacom, Inc.
    MySQL GUI Tools for Online Applications: Eliacom

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts