需要帮助以base64搜索EN codeD MySQL的领域 [英] Need help to search in base64 encoded MySQL field

问题描述

我试图找到在MySQL领域是EN codeD以base64（国际字符）

I am trying to find a string in a MySQL field which is encoded in base64 (for international characters)

常用搜索：

$sql = "SELECT * FROM table WHERE field LIKE '%term%' ";

我已经试过这样：

I have tried this:

$sql = "SELECT * FROM table WHERE field name LIKE '%".base64_encode($term)."%'  ";

但它不工作的时候，根据不同的搜索词长度...出于某种原因，它给结果每当我的任期是一个奇数长...

But it does not work all the time, depending on the search term length... For some reason, it gives result whenever my term is an odd-number long...

我也使用MySQL的函数试图 TO_BASE64（）和 FROM_BASE64（）没有工作..

I have also tried using the MySQL function TO_BASE64() and FROM_BASE64() which did not work...

有人可以帮助？

推荐答案

您需要比较反对的base64德codeD存储值的输入值，因此反转的尝试一点点叫 FROM_BASE64（）上存储的价值和比较的普通输入值。你无法比较 $项部分匹配，如果它是连接codeD，因为部分值永远不会产生相同甚至相似的base64字符串作为完整的保存价值。

You need to compare the input value against the base64-decoded stored value, so reverse your attempt a little to call FROM_BASE64() on the stored value and compare against the plain input value. You cannot compare a partial match in $term if it is encoded because the partial value will never produce the same or even similar base64 string as the full stored value.

SELECT * FROM `table` WHERE FROM_BASE64(`field`) LIKE '%$term%'

在此背景下， $期限是一个简单的字符串，而不是采用base64 EN codeD。这是当然的建议 $期限是绑定的参数，而不是一个简单的PHP变量连接成查询。

In this context, $term is a plain string, not base64 encoded. It's of course recommended that $term be a bound parameter rather than a plain PHP variable concatenated into the query.

然而，这将是的慢的。如果您在任何位置来改变你的数据已被存储的方式，我们强烈鼓励将其存储在一个普通的unen codeD形式。每个查询需要的base64德code的每个的行以找到匹配的，这是非常低效的。

However, this is going to be slow. If you are in any position to change the way your data has been stored, you are highly encouraged to store it in a plain unencoded form. Every query will need to base64-decode every row to find a matching one, which is extremely inefficient.

还要注意， TO_BASE64（），FROM_BASE64（）最近在MySQL中5.6.1 增加，因此在很多设施可能无法使用。你真的应该改变数据存储来消除编码的方式。

Note also, that TO_BASE64(),FROM_BASE64() were recently added in MySQL 5.6.1, and therefore may not be available in a lot of installations. You really should change the way the data is stored to eliminate the encoding.

这篇关于需要帮助以base64搜索EN codeD MySQL的领域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！