Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

character encoding - MySQL Error Code: 1267. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'locate'

I have a MySQL 8 database schema which was originally created with the incorrect collation of "utf8mb4_0900_ai_ci".

When importing tables from another database, compiling the views and running queries against them, I get the error: Error Code: 1267. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'locate'

I then converted collation for the entire database and tables

SELECT @@collation_database; utf8mb4_general_ci

By running SHOW TABLE STATUS FROM <database>; I can confirm that all tables in the database have the correct collation of utf8mb4_general_ci but the error persists.

What should I do to resolve this?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I finally resolved by recreating a few functions that were called as part of the view.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...