https://blog.csdn.net/qq_15041159/article/details/108615959
原文件：
thinkphp\library\think\db\Query.php
2处修改。
//在1300行左右
protected function parseWhereExp($logic, $field, $op, $condition, $param = [], $strict = false)
{
$logic = strtoupper($logic);
if ($field instanceof \Closure) {
$this->options['where'][$logic][] = is_string($op) ? [$op, $field] : $field;
return;
}
if (is_string($field) && !empty($this->options['via']) && !strpos($field, '.')) {
$field = $this->options['via'] . '.' . $field;
}
if ($field instanceof Expression) {
return $this->whereRaw($field, is_array($op) ? $op : []);
} elseif ($strict) {
// 使用严格模式查询
$where[$field] = [$op, $condition];
// 记录一个字段多次查询条件
//******修改：添加相应关键词的排除
$this->options['multi'][$logic][$field][] = $where[$field];
} elseif (is_string($field) && preg_match('/[,=\>\<\'\"\(\s]/', $field) && !in_array(strtolower($op),['match','match in boolean'])) {
$where[] = ['exp', $this->raw($field)];
if (is_array($op)) {
// 参数绑定
$this->bind($op);
}
} elseif (is_null($op) && is_null($condition)) {
if (is_array($field)) {
// 数组批量查询
$where = $field;
foreach ($where as $k => $val) {
$this->options['multi'][$logic][$k][] = $val;
}
} elseif ($field && is_string($field)) {
// 字符串查询
$where[$field] = ['null', ''];
$this->options['multi'][$logic][$field][] = $where[$field];
}
} elseif (is_array($op)) {
$where[$field] = $param;
} elseif (in_array(strtolower($op), ['null', 'notnull', 'not null'])) {
// null查询
$where[$field] = [$op, ''];
$this->options['multi'][$logic][$field][] = $where[$field];
//******修改：添加相应关键词的排除
} elseif (is_null($condition) && !in_array(strtolower($op),['match','match in boolean'])) {
// 字段相等查询
$where[$field] = ['eq', $op];
$this->options['multi'][$logic][$field][] = $where[$field];
} else {
if ('exp' == strtolower($op)) {
$where[$field] = ['exp', $this->raw($condition)];
// 参数绑定
if (isset($param[2]) && is_array($param[2])) {
$this->bind($param[2]);
}
} else {
if(in_array(strtolower($op),['match','match in boolean'])){
$op = strtolower($op);
}
$where[$field] = [$op, $condition];
//echo '$field'.$field.'op'.$op.'$condition'.$condition;
}
// 记录一个字段多次查询条件
$this->options['multi'][$logic][$field][] = $where[$field];
}
if (!empty($where)) {
if (!isset($this->options['where'][$logic])) {
$this->options['where'][$logic] = [];
}
if (is_string($field) && $this->checkMultiField($field, $logic)) {
$where[$field] = $this->options['multi'][$logic][$field];
} elseif (is_array($field)) {
foreach ($field as $key => $val) {
if ($this->checkMultiField($key, $logic)) {
$where[$key] = $this->options['multi'][$logic][$key];
}
}
}
$this->options['where'][$logic] = array_merge($this->options['where'][$logic], $where);
}
}
原文件
thinkphp\library\think\db\Builder.php
//*****25行左右
//添加match、match in boolean两个条件关键词
// 数据库表达式
protected $exp = ['eq' => '=', 'neq' => '<>', 'gt' => '>', 'egt' => '>=',
'lt' => '<', 'elt' => '<=', 'notlike' => 'NOT LIKE', 'not like' => 'NOT LIKE',
'like' => 'LIKE', 'in' => 'IN', 'exp' => 'EXP', 'notin' => 'NOT IN',
'not in' => 'NOT IN', 'between' => 'BETWEEN', 'not between' => 'NOT BETWEEN',
'notbetween' => 'NOT BETWEEN', 'exists' => 'EXISTS', 'notexists' => 'NOT EXISTS',
'not exists' => 'NOT EXISTS', 'null' => 'NULL', 'notnull' => 'NOT NULL',
'not null' => 'NOT NULL', '> time' => '> TIME', '< time' => '< TIME',
'>= time' => '>= TIME', '<= time' => '<= TIME', 'between time' => 'BETWEEN TIME',
'not between time' => 'NOT BETWEEN TIME', 'notbetween time' => 'NOT BETWEEN TIME',
'match' => 'MATCH','match in boolean' => 'MATCH IN BOOLEAN'];
//*****260行左右
//修改where表达式的拼接方案，适配无关键词的情况
//4处修改
public function buildWhere($where, $options)
{
if (empty($where)) {
$where = [];
}
if ($where instanceof Query) {
return $this->buildWhere($where->getOptions('where'), $options);
}
$whereStr = '';
$binds = $this->query->getFieldsBind($options['table']);
foreach ($where as $key => $val) {
$str = [];
foreach ($val as $field => $value) {
if($value == ['like',0]){
echo json_encode($options);
}
if ($value instanceof Expression) {
$str[] = '( ' . $value->getValue() . ' )';
} elseif ($value instanceof \Closure) {
// 使用闭包查询
$query = new Query($this->connection);
call_user_func_array($value, [ & $query]);
$whereClause = $this->buildWhere($query->getOptions('where'), $options);
if (!empty($whereClause)) {
$str[] = '( ' . $whereClause . ' )';
}
} elseif (strpos($field, '|')) {
// 不同字段使用相同查询条件（OR）
$array = explode('|', $field);
$item = [];
foreach ($array as $k) {
$item[] = $this->parseWhereItem($k, $value, '', $options, $binds);
}
$str[] = '( ' . implode(' OR ', $item) . ' )';
} elseif (strpos($field, '&')) {
// 不同字段使用相同查询条件（AND）
$array = explode('&', $field);
$item = [];
foreach ($array as $k) {
$item[] = $this->parseWhereItem($k, $value, '', $options, $binds);
}
$str[] = '( ' . implode(' AND ', $item) . ' )';
} elseif (is_array($value) && in_array($value[0],['match','match in boolean'])){
if(!$value[1]){
$str[] = '';
}else {
$field = is_string($field) ? $field : '';
$str[] = $this->parseWhereItem($field, $value, $key, $options, $binds);
}
}
else {
// 对字段使用表达式查询
$field = is_string($field) ? $field : '';
$str[] = $this->parseWhereItem($field, $value, $key, $options, $binds);
}
}
$whereStr .= empty($whereStr) ? substr(implode(' ', $str), strlen($key) + 1) : implode(' ', $str);
return $whereStr;
}
// where子单元分析
protected function parseWhereItem($field, $val, $rule = '', $options = [], $binds = [], $bindName = null)
{
// 查询规则和条件
if (!is_array($val)) {
$val = is_null($val) ? ['null', ''] : ['=', $val];
}
list($exp, $value) = $val;
//=========== 修改：全文搜索跳过字段分析
if(in_array($exp,['match','match in boolean'])) {
$key = $field;
}else{
// 字段分析
$key = $field ? $this->parseKey($field, $options, true) : '';
}
// 对一个字段使用多个查询条件
if (is_array($exp)) {
$item = array_pop($val);
// 传入 or 或者 and
if (is_string($item) && in_array($item, ['AND', 'and', 'OR', 'or'])) {
$rule = $item;
} else {
array_push($val, $item);
}
foreach ($val as $k => $item) {
$bindName = 'where_' . str_replace('.', '_', $field) . '_' . $k;
$str[] = $this->parseWhereItem($field, $item, $rule, $options, $binds, $bindName);
}
return '( ' . implode(' ' . $rule . ' ', $str) . ' )';
}
// 检测操作符
if (!in_array($exp, $this->exp)) {
$exp = strtolower($exp);
if (isset($this->exp[$exp])) {
$exp = $this->exp[$exp];
} else {
throw new Exception('where express error:' . $exp);
}
}
$bindName = $bindName ?: 'where_' . $rule . '_' . str_replace(['.', '-'], '_', $field);
if (preg_match('/\W/', $bindName)) {
// 处理带非单词字符的字段名
$bindName = md5($bindName);
}
if ($value instanceof Expression) {
} elseif (is_object($value) && method_exists($value, '__toString')) {
// 对象数据写入
$value = $value->__toString();
}
$bindType = isset($binds[$field]) ? $binds[$field] : PDO::PARAM_STR;
if (is_scalar($value) && array_key_exists($field, $binds) && !in_array($exp, ['EXP', 'NOT NULL', 'NULL', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN']) && strpos($exp, 'TIME') === false) {
if (strpos($value, ':') !== 0 || !$this->query->isBind(substr($value, 1))) {
if ($this->query->isBind($bindName)) {
$bindName .= '_' . str_replace('.', '_', uniqid('', true));
}
$this->query->bind($bindName, $value, $bindType);
$value = ':' . $bindName;
}
}
$whereStr = '';
if (in_array($exp, ['=', '<>', '>', '>=', '<', '<='])) {
// 比较运算
if ($value instanceof \Closure) {
$whereStr .= $key . ' ' . $exp . ' ' . $this->parseClosure($value);
} else {
$whereStr .= $key . ' ' . $exp . ' ' . $this->parseValue($value, $field);
}
} elseif ('LIKE' == $exp || 'NOT LIKE' == $exp) {
// 模糊匹配
if (is_array($value)) {
foreach ($value as $item) {
$array[] = $key . ' ' . $exp . ' ' . $this->parseValue($item, $field);
}
$logic = isset($val[2]) ? $val[2] : 'AND';
$whereStr .= '(' . implode($array, ' ' . strtoupper($logic) . ' ') . ')';
} else {
$whereStr .= $key . ' ' . $exp . ' ' . $this->parseValue($value, $field);
}
} elseif ('EXP' == $exp) {
// 表达式查询
if ($value instanceof Expression) {
$whereStr .= '( ' . $key . ' ' . $value->getValue() . ' )';
} else {
throw new Exception('where express error:' . $exp);
}
} elseif (in_array($exp, ['NOT NULL', 'NULL'])) {
// NULL 查询
$whereStr .= $key . ' IS ' . $exp;
} elseif (in_array($exp, ['NOT IN', 'IN'])) {
// IN 查询
if ($value instanceof \Closure) {
$whereStr .= $key . ' ' . $exp . ' ' . $this->parseClosure($value);
} else {
$value = array_unique(is_array($value) ? $value : explode(',', $value));
if (array_key_exists($field, $binds)) {
$bind = [];
$array = [];
$i = 0;
foreach ($value as $v) {
$i++;
if ($this->query->isBind($bindName . '_in_' . $i)) {
$bindKey = $bindName . '_in_' . uniqid() . '_' . $i;
} else {
$bindKey = $bindName . '_in_' . $i;
}
$bind[$bindKey] = [$v, $bindType];
$array[] = ':' . $bindKey;
}
$this->query->bind($bind);
$zone = implode(',', $array);
} else {
$zone = implode(',', $this->parseValue($value, $field));
}
$whereStr .= $key . ' ' . $exp . ' (' . (empty($zone) ? "''" : $zone) . ')';
}
} elseif (in_array($exp, ['NOT BETWEEN', 'BETWEEN'])) {
// BETWEEN 查询
$data = is_array($value) ? $value : explode(',', $value);
if (array_key_exists($field, $binds)) {
if ($this->query->isBind($bindName . '_between_1')) {
$bindKey1 = $bindName . '_between_1' . uniqid();
$bindKey2 = $bindName . '_between_2' . uniqid();
} else {
$bindKey1 = $bindName . '_between_1';
$bindKey2 = $bindName . '_between_2';
}
$bind = [
$bindKey1 => [$data[0], $bindType],
$bindKey2 => [$data[1], $bindType],
];
$this->query->bind($bind);
$between = ':' . $bindKey1 . ' AND :' . $bindKey2;
} else {
$between = $this->parseValue($data[0], $field) . ' AND ' . $this->parseValue($data[1], $field);
}
$whereStr .= $key . ' ' . $exp . ' ' . $between;
} elseif (in_array($exp, ['NOT EXISTS', 'EXISTS'])) {
// EXISTS 查询
if ($value instanceof \Closure) {
$whereStr .= $exp . ' ' . $this->parseClosure($value);
} else {
$whereStr .= $exp . ' (' . $value . ')';
}
} elseif (in_array($exp, ['< TIME', '> TIME', '<= TIME', '>= TIME'])) {
$whereStr .= $key . ' ' . substr($exp, 0, 2) . ' ' . $this->parseDateTime($value, $field, $options, $bindName, $bindType);
} elseif (in_array($exp, ['BETWEEN TIME', 'NOT BETWEEN TIME'])) {
if (is_string($value)) {
$value = explode(',', $value);
}
$whereStr .= $key . ' ' . substr($exp, 0, -4) . $this->parseDateTime($value[0], $field, $options, $bindName . '_between_1', $bindType) . ' AND ' . $this->parseDateTime($value[1], $field, $options, $bindName . '_between_2', $bindType);
} elseif ($exp == 'MATCH'){
//=========== 修改： 全文搜索1
if(!$value){
$whereStr .= '';
}
if ($value instanceof \Closure) {
$whereStr .= 'MATCH(' . $key . ') AGAINST (' . $this->parseClosure($value) . ')';
} else {
$whereStr .= 'MATCH(' . $key . ') AGAINST (' . $this->parseValue($value, $field) . ')';
}
} elseif ($exp == 'MATCH IN BOOLEAN'){
// =========== 修改： 全文搜索2
if(!$value){
$whereStr .= '';
}
if ($value instanceof \Closure) {
$whereStr .= 'MATCH(' . $key . ') AGAINST (' . $this->parseClosure($value) . ' IN BOOLEAN MODE)';
} else {
$whereStr .= 'MATCH(' . $key . ') AGAINST (' . $this->parseValue($value, $field) . ' IN BOOLEAN MODE)';
}
}
return $whereStr;
}
修改文件：
thinkphp/library/think/db/builder/Mysql.php
protected function parseKey($key, $options = [], $strict = false)
函数下
if ($strict && !preg_match('/^[\w\.\*]+$/', $key)) {
throw new Exception('not support data:' . $key);
}
if ('*' != $key && ($strict || !preg_match('/[,\'\"\*\(\)`.\s]/', $key))) {
$key = '`' . $key . '`';
}
改成：
if ($strict && !preg_match('/^[\w\.,\*]+$/', $key)) {
throw new Exception('not support data:' . $key);
}
if ('*' != $key && ($strict || !preg_match('/[,\'\"\*\(\)`.\s]/', $key))) {
if(strstr($key, ',')) {
$keyArray = explode(',', $key);
$tmpList = [];
foreach ($keyArray as $key_) {
$tmpList[] = '`'.$key_.'`';
}
$key = join(',', $tmpList);
} else {
$key = '`' . $key . '`';
}
}
添加后的Thinkphp链式操作示例
以上的SQL可以用Thinkphp的链式操作生成，而且，如果$searchtext为空的情况下，我的代码将不会生成相关的where语句，较符合实际需求
$total = model('Article')
->where('title,summary','MATCH IN BOOLEAN',$searchtext)
->where('status',1)
->count();
$subQuery = Db::table('Article')
->where('title,summary','match in boolean',$searchtext)
->where('post.status',1)
->limit($offset, $limit)
->order($sort, $order)
->select();
使用方法就是
->where(‘字段名称’,‘MATCH IN BOOLEAN’,‘要搜索的词’)
或
->where(‘字段名称’,‘MATCH’,‘要搜索的词’)