在指定列的多关键词搜索（PHP+MYSQL）
| Id | 字段1 | 字段2 | 字段3 | 字段4 | 字段5 | 字段6 | 字段7 | 字段8 | 字段9 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 字段1内容 | 字段2内容 | 字段3内容 | 字段4内容 | 字段5内容 | 字段6内容 | 字段7内容 | 字段8内容 | 字段9内容 |
以上表名为table1
以下函数是依照前端传递来的关键词进行搜索的函数
static function filter ( $request, $columns, &$bindings )
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::pluck( $columns, 'dt' );
if ( isset($request['search']) && $request['search']['value'] != '' ) {
$str = $request['search']['value'];
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['searchable'] == 'true' ) {
if(!empty($column['db'])){
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$globalSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
}
如何修改上述函数可以实现在指定列（字段5 | 字段6 | 字段7 ）实现以下多关键词搜索呢？
比如：前端传递过来的关键词是a b c，各个关键词以空格分开。
情况1：如果字段5里面包含a、字段6里面包含b、字段7里面包含c，那么就搜到了。
情况2：如果字段5 | 字段6 | 字段7里面都不包含a 或b或c，就搜不到。
情况3：仅仅只有字段5里面包含a、字段6里面包含c，也是搜不到的。
情况4：如果字段5里面同时包含a b c也是可以搜到的。
以上4种情况不能涵盖所有情况，但是大概说清了需要满足搜索结果的基本要求。
ps：该函数源自datatables的ssp.class.php ---> https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php
同时，如果实现后，如何提高搜索速度呢？毕竟数据库有50万条数据啊～～
前段html，测试了下应该是差不多了
<!DOCTYPE html>
<html lang="en-US">
<head>
<title>ajax_demo</title>
<meta charset="utf-8">
</head>
<body>
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Extn.</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Extn.</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</tfoot>
</table>
<style>
#dvType {
text-align: center;
font-weight: bold;
line-height: 50px;
}
.r {
color: #f00
}
.dataTables_filter{visibility:hidden}
</style>
<div id="dvType">单选或多选：<input type="radio" name="type" value="0" />重置 <input type="radio" name="type" value="1" />单选搜索 <input type="radio" name="type" value="2" />多选搜索 <input type="button" id="btnMul" style="display:none" value="开始搜索" /></div>
<link href="https://cdn.datatables.net/1.11.1/css/jquery.dataTables.min.css" type="text/css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.11.1/js/jquery.dataTables.min.js"></script>
<script>
$(document).ready(function () {
var headfilter=$('#example thead tr')
.clone(true)
.addClass('filters').appendTo('#example thead'),
filterInput;
var eg = $('#example'), searchBox, type = '0', keywords,
searchCols = [0, 1, 2],//要要处理的列下标，从0开始，0表示第一列，1第二列，依次类推
api;//datatable api
var $dt = eg.DataTable({
"columnDefs": [//这里定义哪些列作为搜索条件
{ targets: searchCols, searchable: true },
{ targets: '_all', searchable: false }
],
"ajax": "x.php",
orderCellsTop: true,
fixedHeader: true,
processing: true,
serverSide:true,
initComplete: function (settings, json) {//加载完毕事件
api = this.api();
api.columns().eq(0).each(function (colIdx) {
var cell = $('.filters th').eq($(api.column(colIdx).header()).index());
if (searchCols.findIndex(function (v) { return v == colIdx }) == -1) {
cell.html('<input type="text" style="display:none" placeholder="' + title + '" />')
return true;//不包含处理的列退出
}
var title = $(cell).text();
cell.html('<input type="text" placeholder="' + title + '" />');
cell.find('input').on('change', function (e) {
api.column(colIdx).search(this.value)//.draw();
});
});
filterInput = headfilter.find(':text');
eg.after($('#dvType'));//插入搜索类型
searchBox = eg.parent().find('.dataTables_filter input');//还是这句的问题，获取查询框出错。该这样测试没问题了
},
rowCallback: function (row) {//关键字高亮
row = $(row);
row.find('span.r').removeClass('r');
if (type == '2' || type == '0') {
row.find(':checkbox' + (type == '0' ? ',span.single' : '')).parent().html(function () { return this.innerText })
}
else {
if (!row.find('span.single').length) {//这行没有格式化过，需要重新格式化
row.find('td').each(function () {
var cellIndex = this.cellIndex;
if (searchCols.findIndex(function (v) { return v == cellIndex }) == -1) return true;//不包含处理的列退出
this.innerHTML = this.innerText.split(' ').map(function (i) {
if (i) return '<span class="single">' + i + '</span>'
return ''
}).join(' ');
});
}
}
//高亮操作
if (keywords) {
var re = new RegExp('(' + keywords.split(' ').join('|') + ')', 'ig');
row.find('td').each(function () {
var cellIndex = this.cellIndex;
if (searchCols.findIndex(function (v) { return v == cellIndex }) == -1) return true;//不包含处理的列退出
if (type == '1') $('span', this).each(function () {
if (re.test(this.innerText)) this.classList.add('r')
});
else this.innerHTML = this.innerText.replace(re, '<span class=r>$1</span>')
});
}
}
});
eg.on('click', 'span.single', function () {
searchBox.val(keywords=this.innerHTML);//执行搜索
searchBox.trigger('input');
}).on('click', ':checkbox', function () {
var cellIndex = this.parentNode.cellIndex, s = filterInput.eq(cellIndex).val();
if (this.checked) {
if (s.indexOf(this.value) == -1) s += (s ? ' ' : '') + this.value;
}
else s = s.replace(' ' + this.value, '').replace(this.value + ' ', '');
filterInput.eq(cellIndex).val(s);
});
var btnMul = $('#btnMul');
btnMul.click(function () {////多选只能通过按钮来触发，要不无法知道用户是否已经选择完所有关键字
keywords=''
filterInput.each(function () {
if (this.value) keywords += ' ' + this.value;
$(this).trigger('change');
});
keywords = keywords.substring(1);
$dt.draw();
});
$('#dvType :radio').click(function () {
type = this.value;
headfilter[type == '1' ? 'hide' : 'show']();
searchBox.val('');
headfilter.find(':text').val('').trigger('change');
if (type == '0') {
searchBox.val(keywords = '');
$dt.search('').draw();
return;
}
$('#btnMul')[type == '2' ? 'show' : 'hide']();
eg.find('tbody td').each(function () {
var cellIndex = this.cellIndex;
if (searchCols.findIndex(function (v) { return v == cellIndex }) == -1) return true;//不包含处理的列退出
this.innerHTML = this.innerText.split(' ').map(function (i) {
if (i) return type == 1 ? '<span class="single">' + i + '</span>' : '<input type="checkbox" value="' + i.replace(/"/g, '"') + '"/>' + i
return ''
}).join(' ');
});
});
});
</script>
意思就是，在指定多个字段中，一行数据只有所有的关键字都存在，才能作为结果返回。
你的字段都是字符串，like本来就是慢，又有这么多条件，50万数据虽然不多，但组合起来肯定慢.
1.如果你能确定每个条件筛选出来的数量不多，那么逻辑可以再代码里处理。
//在字段5中查询a or b or c
//在字段6中查询a or b or c
//查询结果按关键字做key, 数据的主键做value。 比如一条数据主键x中有a,b关键字那么，a=>[x,...] b=>[x,...]
//最后整合数据，使用PHP的函数array_intersect() 求交集，这样就代码中跑出来了。
2.如果数据太多，会占用很多内存。但是数据实时性不高，而且搜索关键字固定，那么加扩展表，用定时任务刷数据到新表中。逻辑如上
3.如果实时性高，而且是随意搜索，那么数据库表就不适合做这件事。建议使用全文搜索引擎，比如ElasticSearch
SELECT
*
FROM
TABLE
WHERE
CONCAT( 字段 5,字段 6,字段 7 ) LIKE '%a%'
AND CONCAT( 字段 5,字段 6,字段 7 ) LIKE '%b%'
AND CONCAT( 字段 5,字段 6,字段 7 ) LIKE '%c%'