如何更改Mysql的连接排序规则 [英] How to change the connection collation of Mysql

问题描述

如何更改mysql数据库的连接排序规则?

How can I change connection collation of mysql database?

我在ubuntu 14中使用Mysql Workbench 5.5和mysql 5.5.

I am using Mysql workbench 5.5 and mysql 5.5 in ubuntu 14.

当我执行存储过程时，发生错误:

When I execute a stored procedure, an error occurs:

错误代码:1267.操作'='的归类(utf8_general_ci，IMPLICIT)和(utf8_unicode_ci，IMPLICIT)的非法混合

Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

我通过互联网进行了搜索，互联网上有一个临时性的解决方案可供修改

I have search though the internet, which has a temp solution that is to amend

COLLATE utf8_unicode_ci;

存储过程中的

.

in the stored procedure.

但是我希望将来为所有存储过程解决此问题.我已经找到

But I want to fix this problem for all stored procedures in the future. I have found

SHOW VARIABLES LIKE 'collation%';

返回此内容.

collation_connection    utf8_general_ci
collation_database  utf8_unicode_ci
collation_server    latin1_swedish_ci

如何将utf8_general_ci更改为utf8_unicode_ci?

推荐答案

查看您的my.cnf，在collation_server附近找到以下内容:

Look into your my.cnf, find the contents below near collation_server:

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake

然后将归类变量更改为:

Then change your collation variables to:

collation_connection    utf8_unicode_ci
collation_server        latin1_swedish_ci

记住要重新启动MySQL服务器服务.

Remember to restart MySQL server service.

对于数据库排序规则，可以使用以下SQL:

For DB collation, you can use the following SQL:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

或者您可以在MySQL Workbench的Alter database屏幕上执行此操作(始终将其更新为最新版本！)

or you can do it at Alter database screen in MySQL Workbench (always update this to the latest version!)

这篇关于如何更改Mysql的连接排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！