MYSQL | ENCODE() outputs blob instead of text

Question

Im trying to encode a simple string by the ENCODE()-function. Using a string it gives me text as output. But using a field it gives me BLOB. How can I get around BLOB and output text for encoded fields?

Here's what happens:

SELECT ENCODE('myText,'myPw')
- Output: baddade4b04e // Goal = This + using fieldname

SELECT ENCODE(Field,'myPw') FROM myTable
- Output: [BLOB - 14B]

---

What I've tried:

SELECT CAST(ENCODE(Field,'myPw') AS CHAR(1000) CHARACTER SET utf8) FROM myTable
- Output: Empty rows!

SELECT CONVERT(ENCODE(Field,'myPw') USING utf8) FROM myTable
- Output: % (Output is 1-2 chars, cant be right)

Imagine I have a column user. Now I want "PaulPeter" being encoded the same regardless of whether Im encoding the string "PaulPeter" or the field user where the value is "PaulPeter".

Could anyone explain this to me? Thanks very much!

Encrypted string:

Encrypted field:

MySQL-Client-Version: 5.5.41
user: text utf8_bin

---

EDIT:

I got another question according decoding here: Click
After being able to encode, I got the same problem there with AES_Encryption. When I encrypt a string I get the output as a string. When encrypting a field with a string-value I get blob. :( Totally annoying.

Answer 1

Your user column is of type TEXT. Try casting just that column to CHAR:

SELECT AES_ENCRYPT(CAST(Field AS CHAR(1000)),'myPw') FROM myTable