环境

• MySQL 8.0.40

问题描述

在查询 mysql.slow_log 表时，无法直观的看到具体的慢SQL，而是看到一串二进制文件

mysql> set global log_output='FILE,TABLE';
Query OK, 0 rows affected (0.00 sec)

-- 模拟一条慢SQL
mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

-- 看到 sql_text 的值为 0x73656C65637420736C656570283229
mysql> select * from mysql.slow_log;
+----------------------------+----------------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+
| start_time                 | user_host                              | query_time      | lock_time       | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text                         | thread_id |
+----------------------------+----------------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+
| 2025-06-09 10:18:15.925166 | super_user[super_user] @  [10.10.2.11] | 00:00:02.000627 | 00:00:00.000000 |         1 |             1 |    |              0 |         0 |       211 | 0x73656C65637420736C656570283229 |   3731274 |
+----------------------------+----------------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+
1 row in set (0.00 sec)

问题解决

问题在于默认存储字段为 BLOB 类型，想要正常查看也很简单，转换一下即可

mysql> select a.*,CONVERT(sql_text USING utf8mb4) AS sql_text from mysql.slow_log a;
+----------------------------+----------------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+-----------------+
| start_time                 | user_host                              | query_time      | lock_time       | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text                         | thread_id | sql_text        |
+----------------------------+----------------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+-----------------+
| 2025-06-09 10:18:15.925166 | super_user[super_user] @  [10.10.2.11] | 00:00:02.000627 | 00:00:00.000000 |         1 |             1 |    |              0 |         0 |       211 | 0x73656C65637420736C656570283229 |   3731274 | select sleep(2) |  -- 这边展示出具体的慢SQL了
+----------------------------+----------------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+----------------------------------+-----------+-----------------+
1 row in set (0.00 sec)