将MySQL连接/服务器设置为utf8mb4而不是uft8会破坏SQLYog中的存储过程 [英] Setting MySQL connection/server to utf8mb4 instead of uft8 is breaking stored procedures in SQLYog

问题描述

我是否尝试打开存储过程或创建一个新的SQLYog会出现以下错误: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

Whether I try to open the stored procedures or create a new one SQLYog is giving the following error: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

他们可以从服务器上正确执行，但是在SQLYog上，他们一直在提供此错误代码.

They can execute correctly from the server, but on SQLYog they keep giving this error code.

我暂时用SET collation_connection = @@collation_database;解决了该问题，但我想知道是否还有更永久的解决方案?

I temporarily solved the issue with SET collation_connection = @@collation_database; but I was wondering if there's a more permanent solution?

推荐答案

来自SQLyog:

SET NAMES 'utf8mb4' COLLATE 'utf8_bin';

Error Code: 1253
COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

尝试:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin';

使用SQLyog Community v12.2.2(64位)和MySQL 5.5.49可以修改(打开)并创建新的存储过程.

Using SQLyog Community v12.2.2 (64 bit) and MySQL 5.5.49 can modify (open) and create new stored procedures without problem.

VERSION()
-----------
5.5.49

SET NAMES 'utf8mb4' COLLATE  'utf8mb4_bin';

SET SESSION collation_connection = 'utf8mb4_bin',
            collation_server = 'utf8mb4_bin',
            collation_database = 'utf8mb4_bin';

SHOW VARIABLES WHERE
    `Variable_Name` != 'character_sets_dir' AND
    (`Variable_Name` LIKE '%CHAR%' OR
    `Variable_Name` LIKE '%COLL%');

Variable_name             Value        
------------------------  -------------
character_set_client      utf8mb4      
character_set_connection  utf8mb4      
character_set_database    utf8mb4      
character_set_filesystem  binary       
character_set_results     utf8mb4      
character_set_server      utf8mb4      
character_set_system      utf8         
collation_connection      utf8mb4_bin  
collation_database        utf8mb4_bin  
collation_server          utf8mb4_bin

这篇关于将MySQL连接/服务器设置为utf8mb4而不是uft8会破坏SQLYog中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！