为什么`set names utf8`会改变`replace(uuid(),…)`调用的行为?

yx2lnoni  于 2021-06-19  发布在  Mysql
关注(0)|答案(0)|浏览(222)

在调查一个迁移失败的问题时,我发现了以下奇怪的行为。使用 SET NAMES utf8 在我的客户会话中 REPLACE(uuid(),'','') 电话。

mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','')                |
+--------------------------------------+
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
| 4b483d57-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> select replace(uuid(),'','') from mysql.user;
+--------------------------------------+
| replace(uuid(),'','')                |
+--------------------------------------+
| 539c0b5c-ecdc-11e8-844f-0242ac120002 |
| 539c0b79-ecdc-11e8-844f-0242ac120002 |
| 539c0b7f-ecdc-11e8-844f-0242ac120002 |
+--------------------------------------+
3 rows in set (0.01 sec)

如您所见,生成的uuid只有在设置 NAMESutf8 . 我发现 SET NAMES utf8 正在通过mysql工作台传递查询。
如果这里有人能解释一下角色集( NAMES )影响产量 REPLACE(UUID(), ...) 电话。提前谢谢。
更新:添加一个片段来证明问题1)不是 UUID() 生成非唯一值2)与 utf8mb4 字符集

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| 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                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid                                 | replace(uuid,'','')                  |
+--------------------------------------+--------------------------------------+
| e41d2fe4-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d3042-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
| e41d309c-ed70-11e8-844f-0242ac120002 | e41d2dc2-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid                                 | replace(uuid,'','')                  |
+--------------------------------------+--------------------------------------+
| e9059092-ed70-11e8-844f-0242ac120002 | e9059117-ed70-11e8-844f-0242ac120002 |
| e90591a1-ed70-11e8-844f-0242ac120002 | e905923e-ed70-11e8-844f-0242ac120002 |
| e9059380-ed70-11e8-844f-0242ac120002 | e90593e1-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> select uuid, replace(uuid,'','') from ( select uuid() as uuid from mysql.user) tmp;
+--------------------------------------+--------------------------------------+
| uuid                                 | replace(uuid,'','')                  |
+--------------------------------------+--------------------------------------+
| ef564f32-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef564fa4-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
| ef565019-ed70-11e8-844f-0242ac120002 | ef564d0c-ed70-11e8-844f-0242ac120002 |
+--------------------------------------+--------------------------------------+
3 rows in set (0.00 sec)

更新2:添加 EXPLAIN 下面的查询跟踪@qbolec建议的实际sql代码。这表明 CONVERT(... using ...) 是非唯一uuid的罪魁祸首。我仍然不明白为什么这不是我期望的行为 CONVERT() 功能。

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select replace(uuid(),'','') from mysql.user;\W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 276     | NULL |    7 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select replace(convert(uuid() using utf8mb4),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select replace(uuid(),'','') from mysql.user;\W
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 276     | NULL |    7 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select replace(uuid(),'','') AS `replace(uuid(),'','')` from `mysql`.`user`
Show warnings enabled.

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题