DB and fields are UTF8_unicode_ci collation - MySql v 5.6

HTML charset is utf 8

Double byte script was entered successfully prior to this issue (Chinese, Japanese, plus Cyrillic etc) when using plain non encrypted input.

But trying to use AES_ENCRYPT on input mangles all non-english script. DB field is BLOB.

 UPDATE mytable SET bodytext=AES_ENCRYPT('任車心是,防少劇和的;子反華不就','aeskey') WHERE t_id=123

produces strings of question marks - English is fine though

I'm not sure if I should try to CONVERT the input to UTF8 first - but when I try that I get an error. Any suggestions of what to review?

  • 3
    Which DBMS is this? MySQL? SQL Server? And which version? You should tag the question with this info as different DBMSes and versions behave differently.
    – dbdemon
    Commented May 8, 2018 at 16:17
  • What do you mean by "double byte script"?
    – Rick James
    Commented May 23, 2018 at 21:15

1 Answer 1


Forcing utf8mb4 does the trick. Run a query against MySQL immediately after making the connection with:

SET NAMES'utf8mb4';

Alternatively, use these settings in your my.cnf file to get the same effect:

default-character-set = utf8mb4

default-character-set = utf8mb4

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

(Note: These settings are defaults (for the most part) in MySQL 8.0 and MariaDB 10.3, so this probably isn't a problem there.)

Chinese (and other) scripts are multibyte - standard utf8 allows 3 bytes per character - fine for plain text but using AES_ENCRYPT ON 3 bytes requires another byte - thus setting utf8mb4 allows the required 4 bytes per character.

  • 1
    Can you clarify how this solves the problem, and where it's applied?
    – RDFozz
    Commented May 8, 2018 at 17:20
  • Updated the answer - I'm not used to answering my own questions so not sure why I got the downvotes. Commented May 8, 2018 at 18:07
  • What? AES_ENCRYPT does not look at characters. It looks at byte strings. You need utf8mb4 because some Chinese characters are 4 bytes.
    – Rick James
    Commented May 23, 2018 at 21:17
  • I don't think those my.cnf settings are an accurate replacement for the SET NAMES.
    – Rick James
    Commented May 23, 2018 at 21:18
  • I didn't add that abut my cnf - some editor here did that. Bad habit if you ask me. (insert paragraph break - oh right you can't add one of the most semantically useful styling breaks) I don't know about utf8mb4 being required for chinese - it's not- the database has been taking Chinese input (and Japanese and Korean) perfectly fine with uft8 for the past two years and outputting to PDF and web - and lots of people have read the output to verify it was fine. Commented May 24, 2018 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.