获取错误操作'='的排序规则(utf8mb4_unicode_ci，IMPLICIT)和(utf8mb4_general_ci，IMPLICIT)的非法混合

问题描述

尝试通过MySQL中的存储过程进行选择时出现以下错误

I am getting the error below when trying to do a select through a stored procedure in MySQL

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%'   or Variable_name LIKE 'collation%';

    'character_set_client',     'utf8'
    'character_set_connection', 'utf8'
    'character_set_database',   'utf8mb4'
    'character_set_filesystem', 'binary'
    'character_set_results',    'utf8'
    'character_set_server',     'utf8mb4'
    'character_set_system',     'utf8'
    'collation_connection',     'utf8_general_ci'
    'collation_database',       'utf8mb4_unicode_ci'
    'collation_server',         'utf8mb4_unicode_ci'

所有表都位于utf8mb4_unicode_ci中，字符集为utf8mb4.

All the tables are in utf8mb4_unicode_ci with the character set utf8mb4.

存储过程代码:

CREATE DEFINER=`dev_user`@`%` PROCEDURE `UPLOAD_ROSTER_FOR_DISTRICT`(IN JOBID INT, IN CHUNKID INT, IN DISTRICTID INT, IN DISTRICTYEARID INT)
BEGIN 

DECLARE STUDENTSADDED INT DEFAULT 0; 
DECLARE CLASSESADDED INT DEFAULT 0; 
DECLARE TEACHERSADDED INT DEFAULT 0; 
DECLARE STUDENTSEDITED INT DEFAULT 0; 
DECLARE CLASSESSEDITED INT DEFAULT 0; 
DECLARE TEACHERSEDITED INT DEFAULT 0; 


SET @JOBID = JOBID;
SET @DISTRICTID = DISTRICTID; 
SET @DISTRICTYEARID = DISTRICTYEARID; 
SET @CHUNKID = CHUNKID;

DROP TEMPORARY TABLE IF EXISTS ROSTER_UPLOAD_SCHOOLS; 
DROP TEMPORARY TABLE IF EXISTS ROSTER_UPLOAD_GRADES; 

CREATE TEMPORARY TABLE ROSTER_UPLOAD_SCHOOLS (

  SCHOOLID INT NOT NULL, 
  SCHOOLCODE VARCHAR(50) NOT NULL, 
  PRIMARY KEY (SCHOOLID)
);

CREATE TEMPORARY TABLE ROSTER_UPLOAD_GRADES (

  GRADEID INT NOT NULL, 
  GRADENAME VARCHAR(50) NOT NULL, 
  PRIMARY KEY (GRADEID)
);

INSERT INTO ROSTER_UPLOAD_SCHOOLS (
  SELECT DISTINCT SCH.SchoolId, SCH.SchoolCode FROM school SCH 
  WHERE SCH.DistrictId = @DISTRICTID AND SCH.IsDeleted = 0 
);

INSERT INTO ROSTER_UPLOAD_GRADES (
  SELECT DISTINCT GRD.GRADEID, GRD.GRADENAME FROM grade GRD 
); 

insert ignore into rosterstatistic (RosterId,ClassesAdded,ClassesModified,TeachersAdded,TeachersModified,StudentsAdded,StudentsModified,
    TotalProcessedRows,TotalRows,CreatedUser,UpdatedUser) values (
  @JOBID, 0,0,0,0,0,0,0,0,null,null
);

UPDATE CLASS CLS 
  INNER JOIN ROSTERUPLOAD_CLASS_DETAILS RCD ON CLS.CLASSCODE = RCD.CLASSCODE AND CLS.DISTRICTYEARID = @DISTRICTYEARID 
  INNER JOIN ROSTER_UPLOAD_SCHOOLS RUS ON CLS.SCHOOLID = RUS.SCHOOLID AND RCD.SCHOOLCODE = RUS.SCHOOLCODE 
  INNER JOIN rosterupload RU ON RU.ROSTERID = RCD.ROSTERID AND RU.ROSTERID = @JOBID AND RU.DISTRICTID = @DISTRICTID  
SET CLS.ClassName = RCD.classname, CLS.ISDELETED = 0, CLS.UpdatedUser = RU.CreatedUser  
WHERE RCD.CHUNKID = @CHUNKID;

SET CLASSESSEDITED = ROW_COUNT(); 



INSERT IGNORE INTO CLASS (SchoolId, DistrictYearId, ClassCode, ClassName, IsDeleted, CreatedUser) (
  SELECT distinct RUS.SCHOOLID, @DISTRICTYEARID, RCD.ClassCode, RCD.CLASSNAME, 0, RU.CreatedUser  
  FROM ROSTERUPLOAD_CLASS_DETAILS RCD, ROSTER_UPLOAD_SCHOOLS RUS, rosterupload RU 
  WHERE (RCD.RosterId = @JOBID AND RCD.SchoolCode = RUS.SCHOOLCODE AND RCD.RosterId = RU.RosterId AND RCD.CHUNKID = @CHUNKID)
); 

SET CLASSESADDED = ROW_COUNT(); 


UPDATE STUDENT STUD 
INNER JOIN rosterupload_student_details RSD ON RSD.STUDENTCODE = STUD.STUDENTCODE AND STUD.DISTRICTID = @DISTRICTID 
INNER JOIN rosterupload RU ON RU.ROSTERID = RSD.ROSTERID AND RU.ROSTERID = @JOBID 
SET STUD.FirstName = RSD.FirstName , STUD.MiddleName = RSD.MiddleName, STUD.LastName = RSD.LastName, STUD.Gender = RSD.Gender, 
        STUD.HomeRoom = RSD.HomeRoom, STUD.BirthDate = RSD.BirthDate, STUD.IEPStatus = RSD.IEPStatus, STUD.LEPStatus = RSD.LEPStatus, 
        STUD.LEPYear = RSD.LEPYear, STUD.Race = RSD.Race, STUD.Language = RSD.Language, STUD.Accommodation = RSD.Accomodation, STUD.ReservedField = RSD.Reserved, 
        STUD.IsDeleted = 0, STUD.UpdatedUser = RU.CreatedUser
WHERE RSD.STUDENTCODE = STUD.STUDENTCODE AND STUD.DISTRICTID = RU.DISTRICTID AND RU.DISTRICTID = @DISTRICTID
    AND RSD.CHUNKID = @CHUNKID 
    AND RSD.STUDENTCODE NOT IN (SELECT RSD2.STUDENTCODE FROM rosterupload_student_details RSD2 
    WHERE RSD2.ROSTERID = @JOBID AND RSD2.CHUNKID < @CHUNKID); 





SET STUDENTSEDITED = ROW_COUNT(); 


INSERT IGNORE INTO STUDENT ( DistrictId, StudentCode, FirstName, MiddleName, LastName, Gender, HomeRoom, 
            BirthDate, IEPStatus, LEPStatus, LEPYear, Race, Language, Accommodation, ReservedField, 
            MD5Hash, IsDeleted, CreatedUser) (

  SELECT distinct @DISTRICTID, STUDENTCODE, FirstName, MiddleName, LastName, Gender, HomeRoom, 
          BirthDate, IEPStatus, LEPStatus, LEPYear, Race, Language, Accomodation, Reserved, '', 0, RU.CreatedUser 
  FROM rosterupload_student_details RSD, rosterupload RU
  WHERE RSD.RosterId = RU.RosterId AND RU.RosterId = @JOBID AND RSD.CHUNKID = @CHUNKID
); 

SET STUDENTSADDED = ROW_COUNT(); 


INSERT INTO student_class_grade (StudentId, ClassId, DistrictYearId, GradeId, IsDeleted, CreatedUser) (
  SELECT distinct STUD.StudentId, CLS.ClassId, @DISTRICTYEARID, RUG.GRADEID, 0, RU.CreatedUser 
  FROM rosterupload RU, rosterupload_student_details RSD, ROSTER_UPLOAD_SCHOOLS RUS, ROSTER_UPLOAD_GRADES RUG, STUDENT STUD, CLASS CLS   
  WHERE RU.ROSTERID = RSD.ROSTERID AND  RSD.SCHOOLCODE = RUS.SCHOOLCODE AND RSD.GRADE = RUG.GRADENAME AND RSD.StudentCode = STUD.StudentCode 
  AND STUD.DistrictId = @DISTRICTID AND CLS.ClassCode = RSD.Classcode AND CLS.SchoolId = RUS.SCHOOLID AND CLS.DistrictYearId = @DISTRICTYEARID 
  AND RU.RosterId = @JOBID 
)
  ON DUPLICATE KEY UPDATE GradeId = RUG.GRADEID, IsDeleted = 0, UpdatedUser = RU.CreatedUser;

UPDATE `USER` USR 
INNER JOIN rosterupload_teacher_details RTD ON RTD.EMPLOYEECODE = USR.TEACHEREMPLOYEECODE 
SET USR.TITLE = RTD.TITLE, USR.FIRSTNAME = RTD.FIRSTNAME, USR.LASTNAME = RTD.LASTNAME, USR.MIDDLENAME = RTD.MIDDLENAME 
WHERE RTD.ROSTERID = @JOBID AND USR.DISTRICTID = @DISTRICTID AND USR.ROLEID = 5 AND RTD.CHUNKID = @CHUNKID; 

SET TEACHERSEDITED = ROW_COUNT(); 


INSERT INTO `USER` (TeacherEmployeeCode, DistrictId, RoleId, FirstName, MiddleName, LastName, EmailAddress, Title, CreatedUser, `uuid`, ActivationEmailPending ) (
  SELECT distinct RTD.EmployeeCode, @DISTRICTID, 5, RTD.FirstName, RTD.MiddleName, RTD.LastName, RTD.EmailAddress, RTD.Title, RU.CreatedUser, RTD.`uuid`, true 
  FROM rosterupload_teacher_details RTD
  join ROSTER_UPLOAD_SCHOOLS RUS on RUS.SCHOOLCODE = RTD.schoolCode 
  join rosterupload RU on ru.rosterid = RTD.rosterid
  WHERE NOT EXISTS (SELECT * FROM `USER` US  
        WHERE US.TeacherEmployeeCode = RTD.EmployeeCode AND US.DistrictId = @DISTRICTID AND US.ROLEID = 5) 
  AND RU.ROSTERID = @JOBID AND RU.DistrictId = @DISTRICTID AND RTD.CHUNKID = @CHUNKID
);

SET TEACHERSADDED = ROW_COUNT(); 

UPDATE rosterstatistic STAT 
SET STAT.ClassesAdded = STAT.ClassesAdded + CLASSESADDED, 
    STAT.ClassesModified = STAT.ClassesModified + CLASSESSEDITED, 
    STAT.TeachersAdded = STAT.TeachersAdded + TEACHERSADDED, 
    STAT.TeachersModified = STAT.TeachersModified + TEACHERSEDITED, 
    STAT.StudentsAdded = STAT.StudentsAdded + STUDENTSADDED, 
    STAT.StudentsModified =  STAT.StudentsModified + STUDENTSEDITED
WHERE STAT.rosterid = @JOBID; 

INSERT INTO teacher_school (UserId, SchoolId, DistrictYearId, StaffCode, IsDeleted, CreatedUser, UpdatedUser) (
  SELECT distinct USR.USERID, RUS.SCHOOLID, @DISTRICTYEARID, RTD.StaffCode, 0, RU.CreatedUser, RU.CreatedUser   
  FROM rosterupload_teacher_details RTD
  JOIN ROSTER_UPLOAD_SCHOOLS RUS ON RUS.SCHOOLCODE = RTD.SCHOOLCODE
  JOIN rosterupload RU ON RU.ROSTERID = RTD.ROSTERID
  JOIN `USER` USR ON USR.TeacherEmployeeCode = RTD.EmployeeCode
  WHERE RU.ROSTERID = @JOBID 
    AND USR.DistrictId = @DISTRICTID 
    AND USR.RoleId = 5 
    AND RTD.CHUNKID = @CHUNKID
) ON DUPLICATE KEY UPDATE STAFFCODE = RTD.StaffCode, IsDeleted = 0, UpdatedUser = RU.CreatedUser; 



INSERT INTO teacher_school_class (TeacherSchoolId,ClassId,IsDeleted,CreatedUser,UpdatedUser) (
  SELECT distinct TS.TeacherSchoolId, CLS.ClassId, 0, RU.CreatedUser, RU.CreatedUser
  FROM rosterupload_teacher_details RTD, ROSTER_UPLOAD_SCHOOLS RUS, `USER` USR, class CLS, teacher_school TS, rosterupload RU  
  WHERE RTD.ClassCode = CLS.ClassCode AND RTD.SchoolCode = RUS.SCHOOLCODE AND CLS.DistrictYearId = @DISTRICTYEARID 
    AND TS.UserId = USR.UserId AND TS.DistrictYearId = CLS.DistrictYearId AND TS.SchoolId = RUS.SchoolId 
    AND RTD.EmployeeCode = USR.TeacherEmployeeCode AND USR.RoleId = 5 AND USR.DistrictId = @DISTRICTID 
    AND RTD.RosterId = RU.RosterId AND RU.RosterId = @JOBID 
) ON DUPLICATE KEY UPDATE IsDeleted = 0, UpdatedUser = RU.CreatedUser;



END

推荐答案

我看到您也在使用临时表.请同时为临时表定义相同的排序规则和字符集.

I see that you are using temporary tables too. Please define the same collation and character sets for temporary tables too. Use column-level collation in the SELECT query itself (also, where ever you put a join)

select * from some_table
where some_column collate 
utf8_general_ci = 'Name of the City';

这应该对您有用.

这篇关于获取错误操作'='的排序规则(utf8mb4_unicode_ci，IMPLICIT)和(utf8mb4_general_ci，IMPLICIT)的非法混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！