在调查一个迁移失败的问题时,我发现了以下奇怪的行为。使用 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只有在设置 NAMES
至 utf8
. 我发现 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.
暂无答案!
目前还没有任何答案,快来回答吧!