How can I change connection collation of mysql database?
I am using Mysql workbench 5.5 and mysql 5.5 in ubuntu 14.
When I execute a stored procedure, an error occurs:
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%';
which return this.
collation_connection utf8_general_ci
collation_database utf8_unicode_ci
collation_server latin1_swedish_ci
how can I change utf8_general_ci
to utf8_unicode_ci
?
Firstly, I think the error message is because of the collation of stored procedure (connection) doesn't match the table. But I was wrong, the reason for the error is because of the collation of the column doesn't match the collation of the table.
I want to change to collation of column to 'utf8_unicode_ci' in my case. So I have run this statement:
Please be aware that change of collation may result in data loss. For me, General -> Unicode, with all English in
varchar
column. There is none.Further reading: http://dev.mysql.com/doc/refman/5.7/en/charset-column.html
http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
http://dev.mysql.com/doc/refman/5.7/en/charset-database.html
Look into your
my.cnf
, find the contents below nearcollation_server
:Then change your collation variables to:
Remember to restart MySQL server service.
For DB collation, you can use the following SQL:
or you can do it at Alter database screen in MySQL Workbench (always update this to the latest version!)