Description:
Trying to select rows from a view with a where clause on a calculated column using a string constant, I'm getting Illegal mix of collations error, with a query like this:
select * from students_class_1 where num = "student1";
How to repeat:
mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
create table students ( id int primary key auto_increment, name varchar(50) not null, age int, class_id int );
insert into students (name, age, class_id) values ('张三', 18, 1), ('李四', 19, 1), ('王五', 17, 2);
set collation_connection='utf8mb4_unicode_ci';
create view students_class_1 as select id,name,concat('student',id) as num from students;
mysql> select * from students_class_1 where num = "student1";
+----+--------+----------+
| id | name | num |
+----+--------+----------+
| 1 | 张三 | student1 |
+----+--------+----------+
1 row in set (0.00 sec)
mysql> set collation_connection='utf8mb4_0900_ai_ci';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from students_class_1 where num = "student1";
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='
Description: Trying to select rows from a view with a where clause on a calculated column using a string constant, I'm getting Illegal mix of collations error, with a query like this: select * from students_class_1 where num = "student1"; How to repeat: mysql> show variables like '%coll%'; +-------------------------------+--------------------+ | Variable_name | Value | +-------------------------------+--------------------+ | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci | +-------------------------------+--------------------+ 4 rows in set (0.00 sec) create table students ( id int primary key auto_increment, name varchar(50) not null, age int, class_id int ); insert into students (name, age, class_id) values ('张三', 18, 1), ('李四', 19, 1), ('王五', 17, 2); set collation_connection='utf8mb4_unicode_ci'; create view students_class_1 as select id,name,concat('student',id) as num from students; mysql> select * from students_class_1 where num = "student1"; +----+--------+----------+ | id | name | num | +----+--------+----------+ | 1 | 张三 | student1 | +----+--------+----------+ 1 row in set (0.00 sec) mysql> set collation_connection='utf8mb4_0900_ai_ci'; Query OK, 0 rows affected (0.00 sec) mysql> select * from students_class_1 where num = "student1"; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='