Mysql 错误：'UNION' 操作的排序规则的非法混合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20456152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql error: Illegal mix of collations for operation 'UNION'
提问by user3080291
I am Boxonix!
我是博森尼克斯！
I am currently making a comment section for my website, and Im tiny a bit in trouble! This "Illegal mix of collations for operation 'UNION'" pops out when i run this query:
我目前正在为我的网站制作评论部分，我有点麻烦！当我运行此查询时，会弹出此“操作'UNION'的非法混合排序规则”：
SELECT *
FROM comments
JOIN users ON comments.user_id = users.id
ORDER BY users.id DESC LIMIT $LIMIT1
UNION
SELECT *
FROM comments
JOIN videos ON comments.video_id = videos.id
I am alredy kind a bit confused, im not using Mysql that often! Please help!
我有点困惑，我不经常使用Mysql！请帮忙！
回答by Nikola Loncar
You can force collation in SELECTpart of query. For example if column videos.commentis utf8_general_ciand comments.commentis utf8_unicode_ciyou can force it to be same:
您可以在SELECT部分查询中强制进行整理。例如，如果 column videos.commentisutf8_general_ci和comments.commentisutf8_unicode_ci你可以强制它相同：
SELECT
comment COLLATE utf8_general_ci
FROM comments
UNION
SELECT
comment
FROM videos
But this has limited use in case that collations can't be translated exactly as you want.
但这在无法完全按照您的需要翻译排序规则的情况下用途有限。
回答by Josafat
To avoid "Illegal mix of collations for operation 'UNION'" Mysql error you must verify that same-index columns have the same type of collation or encoding.
为了避免“操作'UNION'的排序规则的非法混合”Mysql错误，您必须验证相同索引列具有相同类型的排序规则或编码。
回答by staticsan
The problem is that the two sub-queries will have different column sets.
问题在于两个子查询将具有不同的列集。
The first sub-query is going to give you all columns from commentsand all columns from users. The second sub-query is going to give you all columns from commentsand all columns from videos. Unless the two tables usersand videoshave the exact same column definitions, then the UNIONis not going to work.
第一个子查询将为您提供来自 的comments所有列和来自 的所有列users。第二个子查询将为您提供来自 的comments所有列和来自 的所有列videos。除非两个表users，并videos有相同的列定义，则UNION是行不通的。
You need to decide what columns you need from each query and then customise each SELECT ...so that they match.
您需要决定每个查询需要哪些列，然后自定义每个列SELECT ...以使它们匹配。
回答by Haji
You can't use order by statement before union, If you want use first you have to convert the result as temporary table. then say order by condition as below
您不能在联合之前使用 order by 语句，如果要先使用，则必须将结果转换为临时表。然后说按条件排序，如下所示
select * from (SELECT * FROM comments
JOIN users ON comments.user_id = users.id
UNION
SELECT * FROM comments
JOIN videos ON comments.video_id = videos.id) as t order by id DESC LIMIT 1
the important note here is that both users and videos should have same number of column as comments table..Because if you use unionor union allyou should take equal no. of columns with equal data type..
这里重要的一点是，用户和视频都应该有与评论表相同数量的列。因为如果你使用union或者union all你应该取等号。具有相同数据类型的列..
If comments,users,videos has different number of columns then don't user select * from. instead select all columns explicity as follow
如果评论、用户、视频的列数不同，则用户不要选择 * from。而是明确选择所有列，如下所示
If comments table has columns user_id,col1,col2,col3 then
如果评论表有列 user_id,col1,col2,col3 那么
select user_id,col1,col2,col3 from (SELECT c.user_id,col1,col2,col3 FROM
comments c JOIN users u c.user_id = u.id
UNION
SELECT c.user_id,col1,col2,col3 FROM comments c
JOIN videos v ON c.video_id = v.id) as t
order by user_id DESC LIMIT 1
回答by Joke_Sense10
Replace $LIMIT1with 1and perform ORDER BYafter second query.Try this:
替换$LIMIT1为1并ORDER BY在第二次查询后执行。试试这个：
(SELECT *
FROM comments
JOIN users ON comments.user_id = users.id)
UNION
(SELECT *
FROM comments
JOIN videos ON comments.video_id = videos.id)
ORDER BY id DESC LIMIT 1
Note:The union operation requires that each of your two queries have exactly the same number of columns in their result set.
注意：联合操作要求您的两个查询中的每一个在其结果集中具有完全相同的列数。
