对加密列进行排序的不同方法是什么？

Question

我在MySQL中有一个AES加密列，我想对它进行排序并对结果进行分页。

我正在遍历每一行并将其添加到数组中。然后我对数组进行排序。在> 10K行时，这会变得非常慢。

我正在考虑添加一个额外的列，并从0#中添加订单，但我看到的问题是，每次在中间插入一行时，我都必须更新排序。

如果有人有这方面的经验，我只想集思广益。

谢谢

Answer 1

模式

-- drop table encryptIt;
create table encryptIt
(   id int auto_increment primary key,
    ciphertext blob not null
);

按原样从PHP加密到它，或者看到This answer。

现在可以直接从mysql分页，而不需要PHP循环和排序。假设您将秘密your_key提供给mysql。假设您可以获得一个基本的计数，因此知道您的页面计数(每页为25)。

SELECT id,CAST(AES_DECRYPT(ciphertext,'your_key') AS CHAR) AS cleartext
FROM encryptIt
order by cleartext
limit 0,25; -- start at first block of 25, bring back 25
-- limit 2,25; -- start at 3rd block of 25, bring back 25

正常分页使用order和Normal。

关键管理遵循最佳做法。一个模糊的评论，但我把它扔出去了。

Answer 2

想必您的加密行没有任何自然排序顺序。(这是加密它们的部分要点。)

您可以在它们上放置一个自动递增ID列，并使用它进行排序/分页。您还可以在它们上加一个插入时间戳。

但是，如果您想要的只是一个稳定的排序顺序，那么每次检索它们时，它们都会以相同的顺序出现，那么您可以通过加密的值进行排序。如果它们存储在文本或二进制大对象(BLOB)列中，则可以按该列的内容排序。

有一个名为max_sort_length的设置，它控制按顺序在大对象列中考虑的字节数。包含加密数据的列可能可以通过比较前几十个字节来可靠地排序。要做到这一点，在使用ORDER执行查询之前，给出以下命令。

SET max_sort_length 48

阅读这个以获得更多信息。https://dev.mysql.com/doc/refman/5.7/en/blob.html