Mysql 创建联表视图,并进行视图查询
SELECT * from view_idcard;
提示:
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci
原因和解决方法:
1.collation不同
$mysql -u root -p
$show variables where Variable_name like 'collation%';
解决方法:
#进入docker里修改mysql配置,docker重启mysql
$docker exec -it af35448d73cd sh
$vi /etc/mysql/mysql.conf.d/mysqld.cnf
加上:
[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci |
#docker重启mysql
$docker restart af35448d73cd
#进入查看collation是否修改
$mysql -u root -p
$show variables where Variable_name like 'collation%';
2、同样的id_card在不同表中字符集排序不同
1个是:utf8mb4_unicode_ci
1个是:utf8_general_ci
解决方法:
排序规则都修改成:utf8_genearl_ci
修改后正确查询: