在mysql中使用AES_ENCRYPT和AES_DECRYPT进行搜索
在mysql中使用AES_ENCRYPT和AES_DECRYPT进行搜索
提问于 2018-02-25 14:09:04
我使用这种技术对数据库中的某些字段进行了加密：
它工作得很好，但我正面临一个问题。
现在字段的内容已经加密了，我不能用经典的方式来做！
我试着在解密字段上做类似的操作，但是sql不识别这个字段!！
以下是结构(非常简单)：
CREATE TABLE `messages` (
`id` int(11) NOT NULL,
`message` varchar(250) NOT NULL,
`crypt_key` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `messages`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;插入：
INSERT into messages (message) VALUES (AES_ENCRYPT('Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. ', '123456'));简单选择：
SELECT
CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt
FROM messages 太好了，一切都很好！
现在，让我们想象一下，我想在“message”字段中搜索一个LIKE：
SELECT
CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt
FROM messages WHERE decrypt LIKE '%Lorem%'我知道这个错误：
Unknown 'decrypt' field in where clause与此查询相同的错误：
SELECT
CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) AS decrypt
FROM messages WHERE decrypt.message LIKE '%Lorem%'原则上，我有消息加密，解密密钥和解密算法！应该可以在解密字段中通过sql搜索，但我找不到解决方案。
有一个请求堆栈？但这并不是很优化..。
我接受任何解决方案和任何意见！
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-02-25 15:29:31
WHERE子句中不允许使用列别名。但是你可以在词条中使用它们：
SELECT CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt
FROM messages
HAVING decrypt LIKE '%Lorem%'您也可以复制完整的表达式并在WHERE子句中使用它(就像Bernd Buffen在注释中建议的那样)：
SELECT CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt
FROM messages
HAVING CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) LIKE '%Lorem%'但我看不出这里有什么理由重复代码。性能应该是相同的，因为任何一种方式都会执行表扫描。
Stack Overflow用户
发布于 2018-02-25 21:52:00
这不是答案。只有“保罗·明镜”。
最大的区别是，首先用所有行创建结果集，然后用HAVING子句过滤结果。因此，查询需要更多的内存来存储第一个结果。
如果使用WHERE子句，它也是一个完整的表扫描，但是mysql只将结果行放在结果集中。
看看样本。我已经创建了一个只有1000行的表，并且每10行中就有一个"LOREM“。请参见“解释”中的“筛选”列。
mysql> select id,CAST(message AS CHAR(20)) from messages limit 10;
+----+---------------------------+
| id | CAST(message AS CHAR(20)) |
+----+---------------------------+
| 1 | Lorem ipsum dolor si |
| 2 | XXXXX ipsum dolor si |
| 3 | XXXXX ipsum dolor si |
| 4 | XXXXX ipsum dolor si |
| 5 | XXXXX ipsum dolor si |
| 6 | XXXXX ipsum dolor si |
| 7 | XXXXX ipsum dolor si |
| 8 | XXXXX ipsum dolor si |
| 9 | XXXXX ipsum dolor si |
| 10 | XXXXX ipsum dolor si |
+----+---------------------------+
10 rows in set, 10 warnings (0,00 sec)
mysql> EXPLAIN SELECT
-> message decrypt
-> FROM messages
-> HAVING decrypt LIKE '%LOREM%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | messages | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)
mysql>
mysql> EXPLAIN SELECT
-> message decrypt
-> FROM messages
-> WHERE message LIKE '%LOREM%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | messages | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)
mysql>页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48974370
复制相关文章
相似问题
腾讯云开发者
