[possible degradation] UTF-8 string is now printed in `0x` hex form in some cases.

[your-diary]

How to Reproduce

CREATE TABLE t (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    encrypted_email VARCHAR(2048) NOT NULL
);

SET
    @encryption_key = 'my_secret_key';

INSERT INTO
    t (encrypted_email)
VALUES
    (
        HEX(AES_ENCRYPT('foo@example.com', @encryption_key))
    );

SELECT
    AES_DECRYPT(UNHEX(encrypted_email), @encryption_key) AS email
FROM
    t;

Expected:

I'm 100% sure I used to get this result until recently:

+-----------------+
| email           |
+-----------------+
| foo@example.com |
+-----------------+

Actual:

+----------------------------------+
| email                            |
+----------------------------------+
| 0x666f6f406578616d706c652e636f6d |
+----------------------------------+

Possible Cause?

The changelog of 1.48.0 (2026/01/27) says

• Use 0x-style hex literals for binaries in SQL output formats.

• Render binary values more consistently as hex literals.

Are these related?

[rolandwalker]

Hi!

Yes, this is intentional. In #1483, we fixed a bug in which some binary values displayed as text, and other binary values displayed as hex literals, depending on whether the values could be interpreted as UTF-8.

Your expression, simplified as

SELECT AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') AS foo;
┌──────────────────────────────────┐
│ foo                              │
├──────────────────────────────────┤
│ 0x666f6f406578616d706c652e636f6d │
└──────────────────────────────────┘

is indeed binary! We can check this using CHARSET()

SELECT CHARSET(AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key')) AS foo;
┌────────┐
│ foo    │
├────────┤
│ binary │
└────────┘

The docs for AES_DECRYPT():

• https://dev.mysql.com/doc/refman/9.0/en/encryption-functions.html#function_aes-encrypt

explain that

AES_DECRYPT() decrypts the encrypted string crypt_str using the key string key_str, and returns the original (binary) string in hexadecimal format. (To obtain the string as plaintext, cast the result to CHAR. Alternatively, start the mysql client with --skip-binary-as-hex to cause all binary values to be displayed as text.)

So, using the documented recommendation works:

SELECT CAST(AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') AS CHAR) AS foo;
┌─────────────────┐
│ foo             │
├─────────────────┤
│ foo@example.com │
└─────────────────┘

Though I personally would be more explicit with CONVERT … USING:

 SELECT CONVERT(AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') USING utf8mb4) AS foo;
┌─────────────────┐
│ foo             │
├─────────────────┤
│ foo@example.com │
└─────────────────┘

Finally, the display of the original value as a hex literal is consistent with the vendor client.

However, mycli could consider an option like --skip-binary-as-hex which restored the previous behavior, so long as it was not the default.