MySQL联盟非法混合排序

I have the following in my PHP query:

在我的PHP查询中有以下内容:

SELECT tags.tag, theValues.* 
FROM ch09.tbl_tags tags 
RIGHT JOIN ((SELECT 'dog' as 'Vals')
UNION (SELECT 'cat' as 'Vals')) theValues on tags.tag = theValues.Vals

After the "RIGHT JOIN" everything within the brackets are created on the fly from user input.

在“右连接”之后，括号内的所有内容都是从用户输入中创建的。

It worked fine in MySQL 4, but I just had a new computer and installed 5.5, imported the SQL dump as UTF-8. But I get:

它在MySQL 4中工作得很好，但我刚买了一台新电脑，安装了5.5，将SQL转储导入为UTF-8。但我得到:

"Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"

“非法合并排序(latin1_swedish_ci，隐式)和(utf8_general_ci，强制)操作'='”

There's probably a better way to construct my query, all I need is everything the user inputs in one column as "Vals" and if there's a matching tag in tbl_tags.tag then it should be there in a column called "tag" else the tag column is blank.

可能有更好的方法来构造查询，我所需要的就是用户在一列中输入的所有内容作为“Vals”，如果tbl_tags中有匹配的标记的话。那么它应该在一个名为“tag”的列中，否则标记列为空。

I have tried using Collate: http://www.answermysearches.com/mysql-fixing-illegal-mix-of-collations-message/352/, but I simply cannot get it to work.

我尝试过使用Collate: http://www.answersearches.com/mysql -fixing-illegal-mix- collage/352/，但是我就是不能让它正常工作。

So I need to either construct a better query or use the collate function somehow, but how?

所以我需要构造一个更好的查询或者使用排序函数，但是怎么做呢?

Thanks in advance.

提前谢谢。

Craig

克雷格

1 个解决方案

#1

Please see the following question: MySQL Encoding Question

请参见以下问题:MySQL编码问题

I think the problem actually deals with the encoding of the Column and SQL text. To get around this, try using the CONVERT(aaa USING bbb) function as follows in your WHERE clause:

我认为问题实际上是关于列和SQL文本的编码。要解决这个问题，请尝试使用转换(aaa使用bbb)函数，如下面的WHERE子句:

SELECT tags.tag, theValues.* 
  FROM ch09.tbl_tags tags 
 RIGHT JOIN ((SELECT 'dog' as 'Vals')
       UNION (SELECT 'cat' as 'Vals')) theValues
   ON CONVERT(tags.tag USING utf8) = theValues.Vals

Alternatively, change your table's column encoding to be UTF8.

或者，将表的列编码更改为UTF8。

Hope this helps,

希望这有助于

john...

约翰…

#1