SQL排序规则(utf8_general_ci，IMPLICIT)和(latin1_swedish_ci，NUMERIC)的非法混合 [英] SQL Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation

问题描述

尝试解决此错误的NOOB总数.我最近切换了服务器，突然之间曾经正常工作的表/SQL查询现在显示以下错误:

Total NOOB trying to solve this error. I recently switched servers and suddenly a table / sql query which used to work perfectly is now displaying the following error:

>操作'<>'SQL = SELECT ...的排序规则(utf8_general_ci，IMPLICIT)和(latin1_swedish_ci，NUMERIC)的非法混合

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for > operation '<>' SQL=SELECT...

我在表中有一个结构，其中包含TIME字段和INT字段.当我将时间字段数据类型切换为INT而不是TIME时，错误消失了.不幸的是，时间数据也是如此(显示0:00).

I have a structure in the table which includes TIME fields and INT fields. When I switch the time field datatype to INT and not TIME the error disappears. Unfortunately so does the time data (it displays 0:00).

SQL查询如下:

SELECT
    OverallRank,
    First,
    Last,
    Affiliate,
    Part1Number,
    Part1NumberRank,
    Part2Number,
    Part2NumberRank,
    DATE_FORMAT(Part1Time, '%i:%s') as Part1Time, 
    Part1TimeRank,
    Part3Number,
    Part3NumberRank,
    AgeGroup
FROM
    (SELECT
        First,
        Last,
        Affiliate,
        AgeGroup,
        Part1Number,
        Part2Number,
        Part1Time,
        Part3Number,
        Part1NumberRank,
        Part2NumberRank,
        Part1TimeRank,
        Part3NumberRank,
        (Part1NumberRank + Part2NumberRank + Part1TimeRank + Part3NumberRank) AS Total,
        @overallrank:=CASE WHEN @total <> (Part1NumberRank + Part2NumberRank + Part1TimeRank + Part3NumberRank) THEN @overallrank+1 ELSE @overallrank+0 END AS OverallRank,
        @total:=(Part1NumberRank + Part2NumberRank + Part1TimeRank + Part3NumberRank) AS TTL
    FROM
        (SELECT @overallrank:=0) overallrank,
        (SELECT @total:=0) ttl,
            (SELECT
                First,
                Last,
                Affiliate,
                AgeGroup,
                Part1Number,
                Part2Number,
                Part1Time,
                Part3Number,
                Part1NumberRank,
                Part2NumberRank,
                Part1TimeRank,
                @rank4:=CASE WHEN @Part3Number <> Part3Number THEN @rank4+1 ELSE @rank4+0 END AS Part3NumberRank,
                @Part3Number:=Part3Number AS P4
            FROM
                (SELECT @rank4:=0) r4,
                (SELECT @Part3Number:=0) p4,
                (SELECT
                    First,
                    Last,
                    Affiliate,
                    AgeGroup,
                    Part1Number,
                    Part2Number,
                    Part1Time,
                    Part3Number,
                    Part1NumberRank,
                    Part2NumberRank,
                    @rank3:=CASE WHEN @Part1Time <> Part1Time THEN @rank3+1 ELSE @rank3+0 END AS Part1TimeRank,
                    @Part1Time:=Part1Time AS P3
                FROM
                    (SELECT @rank3:=0) r3,
                    (SELECT @Part1Time:=0) p3,
                    (SELECT
                        First,
                        Last,
                        Affiliate,
                        AgeGroup,
                        Part1Number,
                        Part2Number,
                        Part1Time,
                        Part3Number,
                        Part1NumberRank,
                        @rank2:=CASE WHEN @Part2Number <> Part2Number THEN @rank2+1 ELSE @rank2+0 END AS Part2NumberRank,
                        @Part2Number:=Part2Number AS P2
                    FROM
                        (SELECT @rank2:=0) r2,
                        (SELECT @Part2Number:=0) p2,
                        (SELECT
                            First,
                            Last,
                            Affiliate,
                            AgeGroup,
                            Part1Number,
                            Part2Number,
                            Part1Time,
                            Part3Number,
                            @rank1:=CASE WHEN @Part1Number <> Part1Number THEN @rank1+1 ELSE @rank1+0 END AS Part1NumberRank,
                            @Part1Number:=Part1Number AS P1
                        FROM
                            (SELECT @rank1:=0) r1,
                            (SELECT @Part1Number:=0) p1,
                            (SELECT
                                    *
                            FROM 
                                #__results
                            WHERE
                                EventName = '2011EoSummer' AND
                Gender = {$REQUEST:Gender} AND
                ({$REQUEST:Age} = 'Overall' OR AgeGroup = {$REQUEST:Age})
                ORDER BY 
                                Part1Number DESC
                            ) T1
                        ) T2
                    ORDER BY
                            Part2Number DESC
                    ) T3
                    ORDER BY
                            Part1Time ASC
            ) T4
            ORDER BY
                Part3Number DESC
        ) T5
        ORDER BY 
            Total ASC
    ) T6

任何帮助将不胜感激

我试图在我的PHPMyADMIN中运行查询，并得到以下信息:

I tried to run the query in my PHPMyADMIN and got the following:

推荐答案

这是我的托管公司解决的一个问题...这是他们与我们联系后的答复.

This is a issue which was solved by my hosting company... here was their response after being contacted.

似乎您当前正在使用的数据库默认为使用服务器的默认信息.这可能在帐户转移期间或通过php配置发生.我们准备了一个脚本，您可以运行该脚本来快速更改任何数据库的排序规则.有关更多信息，请参见以下内容:

It appears that the database you are currently using defaulted to using the server's default information. This can happen during an account move, or through php configurations. We have prepared a script that you can run to quickly change the collation of any database. Please see the following for more information:

http://www.inmotionhosting.com/support/website/databases/how-to-convert-a-database-to-utf-8

排序规则一旦更新，您就应该能够运行查询而不会出现任何错误.

Once the collation is updated, you should then be able to run your query without any errors.

这篇关于SQL排序规则(utf8_general_ci，IMPLICIT)和(latin1_swedish_ci，NUMERIC)的非法混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！