解决MySQL排序规则不一致导致的报错

报错信息

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation ‘=’

报错原因

collation不兼容，一个是 utf8mb4_0900_ai_ci，另一个是utf8mb4_general_ci。

MySQL8 默认排序规则是 utf8mb4_0900_ai_ci。

可以使用以下语句查询：

SHOW VARIABLES LIKE 'collation%';

解决方式

1. 设置默认排序规则为utf8mb4_general_ci

   SET GLOBAL COLLATION_CONNECTION = 'utf8mb4_general_ci';

2. 修改报错信息涉及单表的排序规则

   ALTER TABLE tabaleName CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

3. 使用Shell脚本批量更改数据库表的排序规则

   #!/bin/bash  
   # MySQL数据库连接信息  
   DB_HOST="localhost"  
   DB_USER="root"  
   DB_PASS="123456"  
   DB_NAME="test"  
     
   # 获取表名的SQL查询语句  
   TABLES_QUERY="SHOW TABLES FROM $DB_NAME"  
     
   # 执行SQL查询并获取结果  
   TABLES=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -N -B -e "$TABLES_QUERY")  
   
   # 遍历执行  
   echo "$TABLES" | while read TABLE; do 
   		echo "开始转换====>$TABLE"  
       echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" | mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME"  
   done

注意事项

1. 注意查看数据库版本，不同的版本有不同的默认设置，也可能会出现语句执行不生效的情况；
2. 规范开发的情况下，测试和正式数据库版本应该是一致的，因为数据版本混乱，我才会遇到这个问题。