MySql 5.7函数UUID（）默认排序规则-排序规则的非法混合 [英] MySql 5.7 Function UUID() default collation - Illegal mix of collations
问题描述
问题： MySQL的uuid（）默认排序规则与已配置的连接排序规则不作比较。
Problem: MySQL's uuid() default collation does not compare to configured connnection collation.
我有一个数据库+表+字段已创建使用字符集：utf-8和排序规则utf8_polish_ci。
I have a database + tables + fields created with charset: utf-8 and collation utf8_polish_ci.
my.cnf如下：
init_connect='SET NAMES utf8 COLLATE utf8_polish_ci'
character-set-server=utf8
collation-server=utf8_polish_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/ |
+--------------------------+----------------------------+
排序规则：
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+----------------+
| Variable_name | Value |
+----------------------+----------------+
| collation_connection | utf8_polish_ci |
| collation_database | utf8_polish_ci |
| collation_server | utf8_polish_ci |
+----------------------+----------------+
现在，使用 uuid（）函数时，出现以下错误返回：
Now, when using the uuid() function, following error is returned:
mysql> select replace(uuid(),'-','');
ERROR 1270 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_polish_ci,COERCIBLE), (utf8_polish_ci,COERCIBLE) for operation 'replace'
发生这种情况是由于 uuid（）的默认排序规则似乎是utf8_general_ci。
This happens, due to uuid()'s default collation seems to be utf8_general_ci.
mysql> select charset(uuid()), collation(uuid());
+-----------------+-------------------+
| charset(uuid()) | collation(uuid()) |
+-----------------+-------------------+
| utf8 | utf8_general_ci |
+-----------------+-------------------+
有没有办法更改uuid（）使用的默认排序规则，使其与 collation_connection ？
Is there a way, to change the default collation used by uuid() so that it matches the collation_connection?
在我们的环境中，我们编写SQL更新，这些更新在具有不同排序规则的不同MySQL数据库上执行。因此，通过指定排序规则来强制排序不是一种选择。
In our environment we write SQL updates that are executed on different MySQL databases with different collations. Therefore, to force a collation by specifying it is not an option.
推荐答案
（这实际上不是答案，而是尝试
(This is not really an answer, but an attempt at isolating what causes the problem and what might fix it.)
获取 DATABASE 与完全不相关的字符集和收藏。
Get in a DATABASE with a totally irrelevant CHARACTER SET and COLLATION.
mysql> CREATE DATABASE `so40064402` /*!40100 DEFAULT CHARACTER SET ucs2 COLLATE ucs2_bin */
mysql> USE so40064402;
Database changed
为客户端建立utf8_polish：
Establish utf8_polish for the client:
mysql> SET NAMES utf8 COLLATE utf8_polish_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'c%a%t%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 | -- from SET NAMES
| character_set_connection | utf8 | -- from SET NAMES
| character_set_database | ucs2 | -- from DATABASE
| character_set_filesystem | binary | -- (constant)
| character_set_results | utf8 | -- from SET NAMES
| character_set_server | utf8mb4 |
| character_set_system | utf8 | -- (constant)
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_polish_ci | -- from SET NAMES
| collation_database | ucs2_bin | -- from DATABASE
| collation_server | utf8mb4_unicode_520_ci |
+--------------------------+----------------------------+
11 rows in set (0.00 sec)
mysql> select charset(uuid()), collation(uuid());
+-----------------+-------------------+
| charset(uuid()) | collation(uuid()) |
+-----------------+-------------------+
| utf8 | utf8_general_ci | -- part of the problem, but can't fix this
+-----------------+-------------------+
1 row in set (0.00 sec)
mysql> select replace(uuid(),'-','');
ERROR 1270 (HY000): Illegal mix of collations
(utf8_general_ci,COERCIBLE),
(utf8_polish_ci,COERCIBLE),
(utf8_polish_ci,COERCIBLE) for operation 'replace'
mysql>
mysql>
mysql>
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_polish_ci;
Query OK, 0 rows affected (0.00 sec)
现在让我们更改仅设置名称。现在可以了！？尽管 UUID（）是 utf8 ！？
Now let's change SET NAMES only. Now it works!?? In spite of UUID() being utf8!?
mysql> SHOW VARIABLES LIKE 'c%a%t%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 | -- from SET NAMES
| character_set_connection | utf8mb4 | -- from SET NAMES
| character_set_database | ucs2 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 | -- from SET NAMES
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8mb4_polish_ci | -- from SET NAMES
| collation_database | ucs2_bin |
| collation_server | utf8mb4_unicode_520_ci |
+--------------------------+----------------------------+
11 rows in set (0.00 sec)
mysql> select replace(uuid(),'-','');
+----------------------------------+
| replace(uuid(),'-','') |
+----------------------------------+
| ea841aacf83b11e8a66580fa5b3669ce |
+----------------------------------+
1 row in set (0.00 sec)
mysql>
这篇关于MySql 5.7函数UUID（）默认排序规则-排序规则的非法混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
