MySQL的＆放大器; AJAX predictive搜索实现选项 [英] MySQL & AJAX Predictive Search Implementation Options
问题描述
我有一个MySQL数据库中的地址的地址表。该表中包含地址ID列和地址相关的列的常见品种 - 名，1号线，2号线，郊区，国家，交code等许多领域都允许NULL
I have addresses in an "address" table in a MySQL database. The table contains an address ID column and the usual assortment of address-related columns - name, line 1, line 2, suburb, state, postcode, etc. A number of the fields allow NULL.
这是用在客户端基于Web的界面 - 用户可以找到并通过输入任何部分到文本框中选择从表中的地址。匹配被显示，并且用户可以选择一个。
This is used in a client side web-based interface - the user can find and select an address from the table by typing any part of it into a text box. Matches are displayed and the user can select one.
在文本框中的术语被视为一个空间分隔一系列搜索词，并且该术语的每一个都必须是present在任何地址字段为它显示以供选择。
The terms in the text box are treated as a space-delimited series of search terms, and that each one of the terms must be present in any of the address fields for it to show up for selection.
我是撕了几个实现方法之间的这样的：
I'm torn between a few implementation methods for this:
目前的方法：
- 在页面加载，一个异步HTTP请求（AJAX）来检索一个很好的格式化列表中的所有地址（所有字段合并成一个单一的线，占空字段）。
- jQuery的用于查找和显示在该列表中每当文本框接收输入相匹配。
此方法的好处是，只有一个简单的查询需要被发送，而且也输入到搜索字段，看到的响应，因为进行查找在客户端（尽管这可能不很好地扩展之间没有延迟 - 见下文）。这也避免了复杂的SQL检索（这我不反对，我只是想实现的东西作为一个概念证明这种方法是更快）。
The benefit of this approach is that only one simple query needs to be sent, and there's no delay between typing into the search field and seeing the responses since the searching is done on the client side (although this may not scale well - see below). It also avoids the need for complex SQL searching (which I'm not averse to, I just wanted to implement something as a proof of concept and this method was quicker).
缺点当然是当网页加载的页面必须检索每一个地址，数据库很可能最终存储地址十万。
The downside is of course that the page must retrieve every address when the page is loaded, and the database is likely to end up storing thousands of addresses.
的另一种方法是将发送一个HTTP请求每当用户键入到文本框，这将返回匹配的搜索使用SQL表的地址。需要更多的请求和更大的延迟，但只需要检索并每次传送地址的子集。我可以根据需要轻松调整的最低刑期的长度和轮询频率。
The alternative method would be to send a HTTP request whenever the user types into the text box, which would return the addresses matching a search of the table using SQL. Requires more requests and greater delays, but only needs to retrieve and transfer a subset of addresses each time. I can easily tweak the minimum term length and polling frequency as needed.
我想了解一下实行这个SQL方面的最佳途径......
I'm wondering about the best way to implement the SQL side of this...
我建议最好是去创建一个视图，它并置所有可搜索的地址栏和查询中使用一个WHERE沿concatcolumn LIKE'％TERM1％'和concatcolumn LIKE'％TERM2％'和concatcolumn LIKE'％的线路条款termN％'？
Would I be best off creating a view which concatenates all searchable address columns and using a query with a WHERE clause along the lines of "concatcolumn LIKE '%term1%' AND concatcolumn LIKE '%term2%' AND concatcolumn LIKE '%termN%'"?
任何意见或建议，会大大AP preciated。
Any ideas or suggestions would be much appreciated.
推荐答案
我来，我是非常愉快的一个解决方案。
I've come to a solution that I'm fairly happy with.
首先创建一个包含一个视图可搜索的地址字段连接起来：
Firstly created a view containing the searchable address fields concatenated:
CREATE VIEW address_concat AS
SELECT address_id, CONCAT_WS(' ', address_name, address_line_1, address_line_2, suburb, postcode, state, country) AS full_address
FROM address
当接收到一个搜索字符串的请求，我解析它在PHP和使用的视角以寻找匹配（以下code被清理并删除不相关的东西 - 数据消毒等）：
When a request with a search string is received, I parse it in PHP and use the view to find matches (the following code is cleaned up and removes non-relevant things - the data is sanitised, etc):
$terms = explode(' ', preg_replace('/\s+/', ' ', $_GET['find_address'])); //get array of terms
$where = " WHERE full_address LIKE '%".implode("%' AND full_address LIKE '%", $terms)."%' "; //turn array into WHERE clause
//query database to find matches
$result = $db->query("SELECT a.* FROM address a JOIN address_concat ac ON a.address_id = ac.address_id ".$where." ORDER BY IFNULL(IF(address_name = '', NULL, address_name), address_line_1)");
if ($result->num_rows > 0)
{
//construct output
$output = '<ul>';
while ($row = $result->fetch_assoc())
$output .= '<li val_id="'.$row['address_id'].'">'.make_address(...).'</li>';
$output .= '</ul>';
}
else
$output = '<p>No matches found.</p>';
在客户端，我用一些智慧<一个href="http://stackoverflow.com/questions/4220126/run-javascript-function-when-user-finishes-typing-instead-of-on-key-up">other <一href="http://stackoverflow.com/questions/14042193/how-to-trigger-an-event-in-input-text-after-i-stop-typing-writting">questions使它只在打字暂停后查询。
On the client side, I used some wisdom from other questions to make it only query after a pause in typing.
var typingTimer;
$('input#filterbox').bind('input', function ()
{
var textfield = $(this);
//code to hide/show "clear text field" box eliminated, etc
clearTimeout(typingTimer);
typingTimer = setTimeout(function () {doFilterUpdate(textfield);}, 750);
});
function doFilterUpdate (target)
{
if (target.val().length >= 3)
$('div#resultlist').load('inc/ajax.php?find_address=' + encodeURIComponent(target.val()));
else
$('div#resultlist').html('');
}
我还不能确定，如果在创建视图是必要的/更高效的不仅仅是写在使用CONCAT_WS在地址表中查询的WHERE子句......它使查询简单了很多，虽然：P
I'm still not certain if creating the view is necessary/more efficient than just writing a query on the address table that uses CONCAT_WS in the WHERE clause... it does make the query a lot simpler, though :P
这篇关于MySQL的＆放大器; AJAX predictive搜索实现选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
