mysql aes_encrypt 错误,在mysql中的AES_ENCRYPT之后无法使用AES_DECRYPT

I created user table

CREATE TABLE `user` (

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,

`first_name` VARBINARY(100) NULL ,

`address` VARBINARY(200) NOT NULL ,

PRIMARY KEY (`id`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8

COLLATE = utf8_general_ci;

I inserted one row:

INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'usa2010'));

To select this row i used:

SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user;

I am getting the following result.What i need to do see my data.No data is visible for me.

解决方案

According to the Manual:

AES_ENCRYPT() encrypts a string and returns a binary string.

AES_DECRYPT() decrypts the encrypted string and returns the original string.

I don't know why it is still returning a binary string in your case. Anyway, try this:

SELECT *,

CAST(AES_DECRYPT(first_name, 'usa2010') AS CHAR(50)) first_name_decrypt

FROM user

And use first_name_decrypt instead of first_name.