MySQL 存储过程中排序规则 (utf8_general_ci,IMPLICIT) 和 (utf8_unicode_ci,IMPLICIT) 的非法混合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9197300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) within stored procedure
提问by Packet Tracer
All the tables are in
utf_unicode_ci.I've done this to check
SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE collation_name <> 'utf8_unicode_ci' AND table_schema LIKE 'my_database' ORDER BY table_schema, table_name, ordinal_position;And converted every table just in case
ALTER TABLE `my_database`.`table_name` DEFAULT COLLATE utf8_unicode_ci; ALTER TABLE `my_database`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;My database collation settings are in
utf8_unicode_ci.charsets are
mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.02 sec)
collations are
mysql> show variables like 'colla%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_unicode_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec)
the error is triggered whether I call the stored procedure via web browser or via mysql bash client. just in case my ubuntu/linux locale settings are:
$ locale LANG=es_ES.UTF-8 LANGUAGE=es_ES.UTF-8 LC_CTYPE=es_ES.UTF-8 LC_NUMERIC="es_ES.UTF-8" LC_TIME="es_ES.UTF-8" LC_COLLATE=es_ES.UTF-8 LC_MONETARY="es_ES.UTF-8" LC_MESSAGES=es_ES.UTF-8 LC_PAPER="es_ES.UTF-8" LC_NAME="es_ES.UTF-8" LC_ADDRESS="es_ES.UTF-8" LC_TELEPHONE="es_ES.UTF-8" LC_MEASUREMENT="es_ES.UTF-8" LC_IDENTIFICATION="es_ES.UTF-8" LC_ALL=
所有的表都在
utf_unicode_ci.我这样做是为了检查
SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE collation_name <> 'utf8_unicode_ci' AND table_schema LIKE 'my_database' ORDER BY table_schema, table_name, ordinal_position;并转换每张桌子以防万一
ALTER TABLE `my_database`.`table_name` DEFAULT COLLATE utf8_unicode_ci; ALTER TABLE `my_database`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;我的数据库整理设置在
utf8_unicode_ci.字符集是
mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.02 sec)
排序规则是
mysql> show variables like 'colla%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_unicode_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec)
无论我是通过 Web 浏览器还是通过 mysql bash 客户端调用存储过程，都会触发错误。以防万一我的 ubuntu/linux 语言环境设置是：
$ locale LANG=es_ES.UTF-8 LANGUAGE=es_ES.UTF-8 LC_CTYPE=es_ES.UTF-8 LC_NUMERIC="es_ES.UTF-8" LC_TIME="es_ES.UTF-8" LC_COLLATE=es_ES.UTF-8 LC_MONETARY="es_ES.UTF-8" LC_MESSAGES=es_ES.UTF-8 LC_PAPER="es_ES.UTF-8" LC_NAME="es_ES.UTF-8" LC_ADDRESS="es_ES.UTF-8" LC_TELEPHONE="es_ES.UTF-8" LC_MEASUREMENT="es_ES.UTF-8" LC_IDENTIFICATION="es_ES.UTF-8" LC_ALL=
The only way I've been able to solve this issue is using convertinside each query that causes the error (or using COLLATEinside the query), but the problem is that there are a lot of quite complex stored procedures so it's hard to identify the "bad" queries and takes a lot of time.
我能够解决这个问题的唯一方法是convert在导致错误的每个查询中使用（或COLLATE在查询中使用），但问题是有很多非常复杂的存储过程，因此很难识别“坏”查询并花费大量时间。
I guess that somehow the variables passed to the stored procedure from my system (ubuntu : mysql client, browser), are being sent in utf8_general_ci, so it makes conflict with ut8_unicode_ci from my database.
我猜想从我的系统（ubuntu：mysql 客户端，浏览器）传递给存储过程的变量以某种方式在 utf8_general_ci 中发送，因此它与我的数据库中的 ut8_unicode_ci 发生冲突。
It seems that the os is working with utf8_general_ci even though the mysql connection is set to utf_unicode_ci.
即使 mysql 连接设置为 utf_unicode_ci，操作系统似乎也在使用 utf8_general_ci。
回答by Packet Tracer
I solved my problem, and was due to a wrong conversion during migration, I was converting to utf_general_ci instead of utf8_unicode_ci, so though the mysql database structure was correct the source data was encoded in the wrong encoding (utf8_general_ci) and inserted in the mysql ddbb that way.
我解决了我的问题，由于迁移过程中转换错误，我转换为utf_general_ci而不是utf8_unicode_ci，所以尽管mysql数据库结构正确，但源数据以错误的编码（utf8_general_ci）编码并插入mysql ddbb那样。
So the thing is that you can have the right character set and collation in your mysql database and still get this "Illegal mix of collations" error, because of the data is enconded with another collation.
所以问题是你可以在你的 mysql 数据库中有正确的字符集和排序规则，但仍然得到这个“非法混合排序规则”错误，因为数据是用另一个排序规则编码的。
Hope this helps to somebody in the future.
希望这对将来的某人有所帮助。
回答by ohcnim
in case it helps someone, we had the same error while doing a query in different databases of different servers, the one with the error was a migration from the other. In our case it was fixed by changing the "collation_server" in mysql.ini and restarting the mysql service.
万一它对某人有帮助，我们在不同服务器的不同数据库中进行查询时遇到了同样的错误，有错误的一个是从另一个迁移。在我们的例子中，它是通过更改 mysql.ini 中的“collation_server”并重新启动 mysql 服务来修复的。
回答by ymgoldman
Re-importing stored procs and stored functions once you have your encoding and collation settings right will resolve the issues. Ran into the same exact problem. Another helpful mysql native function for troubleshooting: select collation(some_col) from some_table, if you suspect issues with your table data format.
一旦您拥有正确的编码和排序规则设置，重新导入存储的过程和存储的函数将解决问题。遇到了同样的问题。另一个用于故障排除的有用的 mysql 本机功能：如果您怀疑表数据格式有问题，则从 some_table 中选择 collation(some_col)。
回答by murlid05
I have solved my problem after adding the CHARSET utf8 in a parameter list. may be this will help full to other,
在参数列表中添加 CHARSET utf8 后，我解决了我的问题。可能这将有助于其他人，
PROCEDURE `USP_USR_AuthenticateUser`(
IN ip_username VARCHAR(50) CHARSET utf8**,
IN ip_pwd NVARCHAR(256)
)
