Illegal mix of collations (utf8_general_ci,IMPLICIT) and (ut 原创

多表查询设计表时字符串varchar类型字段选择编码方式时不一致导致的

### SQL: SELECT c.id, c.name, c.d_id FROM lx_admin_dict_detail c WHERE c.id IN ( SELECT category_id AS id FROM lx_article_category a WHERE a.article_id=? UNION SELECT tag_id AS id FROM lx_article_tag b WHERE b.article_id=? );
### Cause: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf32_general_ci,IMPLICIT) for operation '='

给所有表统一设置表字符集存储过程。

复制收展BashDROP PROCEDURE IF EXISTS updateTableCharacter;
CREATE PROCEDURE updateTableCharacter()
BEGIN
    -- 给所有表统一设置表字符集存储过程
    -- 定义表名变量
    DECLARE s_tablename VARCHAR(100);
    DECLARE cur_table_structure CURSOR
        FOR
        SELECT table_name -- 表名
        FROM INFORMATION_SCHEMA.TABLES
        WHERE table_schema = 'leixing_blog'  -- 数据库名称
        and table_name not in ('user_view');

    /*02000 主要代表的意思可以理解为：
   发生下述异常之一：
   SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
   在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
   在 FETCH 语句中引用的游标位置处于结果表最后一行之后。*/
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename = NULL;
    OPEN cur_table_structure;
    FETCH cur_table_structure INTO s_tablename;
    WHILE (s_tablename IS NOT NULL)
    DO

    SET @MyQuery = CONCAT('alter table ',s_tablename,' convert to character set  utf8  collate utf8_general_ci;');
    PREPARE msql FROM @MyQuery;
    EXECUTE msql;

    FETCH cur_table_structure INTO s_tablename;
    END WHILE;
    CLOSE cur_table_structure; -- 关闭游标
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32