mysql aes_encrypted数据比较

Question

I want to compare data encrypted with mysql aes_encrypt() against the encrypted data sent from my app

MySQL version: 10.2.43-MariaDB
Test data: 'abc'
password: '123'
encrypted data sent from my app: 0x911ff5b9a15aae9b52e7e9fde75315b1 

The tests table contains one single record and the column varbinary50 contains data encrypted with mysql's aes_encrypt():

CREATE TABLE `tests` (
  `test_id` int(11) NOT NULL,
  `varbinary50` varbinary(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tests`
--

INSERT INTO `tests` (`test_id`, `varbinary50`) VALUES
(6, 0x911ff5b9a15aae9b52e7e9fde75315b1);

Since varbinary50 contains exactly the value '0x911ff5b9a15aae9b52e7e9fde75315b1', I希望以下查询返回1；取而代之的是0。

select varbinary50='0x911ff5b9a15aae9b52e7e9fde75315b1' from tests

有人可以告诉我我做错了什么吗？ tia

Answer 1

解决方案：将十六进制应用于Varbinary50并从数据中删除“ 0x”前缀

  select lower(hex(varbinary50))='911ff5b9a15aae9b52e7e9fde75315b1' from tests