Question 246301 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to change the default CHARACTER SET and COLLATE for variables/constants?

I have a user table as follow:

CREATE TABLE `user` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL
  -- more column
);

Now when I run the following procedure:

CREATE PROCEDURE myProc ()
BEGIN
   SET @searchTerm = '%';
   SELECT * FROM user WHERE FirstName like @searchTerm;
END

I get the exception:

Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and
(utf8mb4_0900_ai_ci,IMPLICIT) for operation ‘like’ 0.000 sec

I have set the character CHARACTER and COLLATE for the entire DB as follow:

ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

Not sure why my variable @searchTerm has utf8mb4_0900_ai_ci for COLLATE?

Update

Originally, when I asked the question, I did not mention that I was running the following script inside the stored procedure:

SET @searchTerm = '%';
SELECT * FROM user WHERE FirstName like @searchTerm;

I had to update the question, as it turned out that this was the issue.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue was that I had to drop and recreate the stored procedure after changing the CHARACTER SET and COLLATE, i.e. running the following script:

    ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

    Thanks to @Paul Maxwell comment, about restarting the DB, which was a good hint to find the issue.


  2. 0

    The issue is the collation mismatch between the FirstName column and the @searchTerm variable is due to MySQL’s collation handling rules.

    In your query the FirstName column is using the utf8mb4_unicode_520_ci collation, but the @searchTerm variable is using the utf8mb4_0900_ai_ci collation. When you perform a LIKE comparison between these two with different collations, you get the Illegal mix of collations error.

    You have to ensure that the variable @searchTerm uses the same collation as the FirstName column.

    SET @searchTerm = '%';
SET @searchTerm COLLATE utf8mb4_unicode_520_ci; -- Set the collation explicitly
SELECT * FROM user WHERE FirstName LIKE @searchTerm;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search