MySQL字符集错误

django.db.utils.OperationalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

以上报错很明显是由于字符集部匹配导致的。所以登入MySQL修改相关配置。
首先查看MySQL的数据库配置:

mysql> show variables like '%coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | latin1_swedish_ci    |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci   |
+----------------------+--------------------+

修改数据库配置为utf8字符集:

SET collation_connection = utf8_general_ci;
SET collation_database = utf8_general_ci;
SET collation_server = utf8_general_ci;

但是访问服务后错误没变，那么查看表的字符集

mysql> show create table test;
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET latin1 NOT NULL,
  `depth` int(11) NOT NULL,
  `url` varchar(200) CHARACTER SET latin1 NOT NULL,
  `creater_id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `app` varchar(20) CHARACTER SET latin1 NOT NULL,
  `createtime` datetime(6) NOT NULL,
  `operatetime` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_name_parent_id_96346d13_uniq` (`name`,`parent_id`),
  KEY `test_creater_id_0825cbdf_fk_auth_user_id` (`creater_id`),
  KEY `test_parent_id_03eda650_fk_test_id` (`parent_id`),
  CONSTRAINT `test_creater_id_0825cbdf_fk_auth_user_id` FOREIGN KEY (`creater_id`) REFERENCES `auth_user` (`id`),
  CONSTRAINT `test_parent_id_03eda650_fk_test_id` FOREIGN KEY (`parent_id`) REFERENCES `test` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

看到表的字符集已经是utf8mb4，接下来查看各字段的配置：

mysql> show full columns from dataset_folder;
+-------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | int(11)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name        | varchar(200) | latin1_swedish_ci | NO   | MUL | NULL    |                | select,insert,update,references |         |
| depth       | int(11)      | NULL              | NO   |     | NULL    |                | select,insert,update,references |         |
| url         | varchar(200) | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| creater_id  | int(11)      | NULL              | NO   | MUL | NULL    |                | select,insert,update,references |         |
| parent_id   | int(11)      | NULL              | YES  | MUL | NULL    |                | select,insert,update,references |         |
| app         | varchar(20)  | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| createtime  | datetime(6)  | NULL              | NO   |     | NULL    |                | select,insert,update,references |         |
| operatetime | datetime(6)  | NULL              | NO   |     | NULL    |                | select,insert,update,references |         |
+-------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

看到字段的字符还是latin1_swedish_ci，转为utf8:

mysql> alter table dataset_folder convert to character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show full columns from dataset_folder;                                                                                                                                                               +-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name        | varchar(200) | utf8mb4_general_ci | NO   | MUL | NULL    |                | select,insert,update,references |         |
| depth       | int(11)      | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| url         | varchar(200) | utf8mb4_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| creater_id  | int(11)      | NULL               | NO   | MUL | NULL    |                | select,insert,update,references |         |
| parent_id   | int(11)      | NULL               | YES  | MUL | NULL    |                | select,insert,update,references |         |
| app         | varchar(20)  | utf8mb4_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| createtime  | datetime(6)  | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| operatetime | datetime(6)  | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
9 rows in set (0.00 sec)

可以看到字段的配置也修改了。
再次访问服务，成功返回数据。