Laravel数据库查询中对 like 的值进行转义
在laravel开发中,如果我们用到like模糊搜索
$where[] = ['title', 'like', '%'.$data['title'].'%'];
上面这种在laravel中会报数据错误。
参考各种实例,可以这样做
function like_str($str) { $like_escape_char = '!'; return str_replace([$like_escape_char, '%', '_'], [ $like_escape_char.$like_escape_char, $like_escape_char.'%', $like_escape_char.'_', ], $str); }
先用函数对keyword进行处理避免%和_把所有数据查出来
然后如下使用
$query->where('title','like' , "%".like_str($keyword)."%");
或者还可以使用trait
<?php namespace App\Models\Traits; trait LikeScope { /** * @param \Illuminate\Database\Eloquent\Builder $query * @param $column * @param $value * @param $side * @param $isNotLike * @param $isAnd * @return \Illuminate\Database\Eloquent\Builder */ public function scopeLike($query, $column, $value, $side = 'both', $isNotLike = false, $isAnd = true) { $operator = $isNotLike ? 'not like' : 'like'; $escape_like_str = function ($str) { $like_escape_char = '!'; return str_replace([$like_escape_char, '%', '_'], [ $like_escape_char.$like_escape_char, $like_escape_char.'%', $like_escape_char.'_', ], $str); }; switch ($side) { case 'none': $value = $escape_like_str($value); break; case 'before': case 'left': $value = "%{$escape_like_str($value)}"; break; case 'after': case 'right': $value = "{$escape_like_str($value)}%"; break; case 'both': case 'all': default: $value = "%{$escape_like_str($value)}%"; break; } return $isAnd ? $query->where($column, $operator, $value) : $query->orWhere($column, $operator, $value); } public function scopeOrLike($query, $column, $value, $side = 'both', $isNotLike = false) { return $query->like($column, $value, $side, $isNotLike, false); } public function scopeNotLike($query, $column, $value, $side = 'both', $isAnd = true) { return $query->like($column, $value, $side, true, $isAnd); } public function scopeOrNotLike($query, $column, $value, $side = 'both') { return $query->like($column, $value, $side, true, false); } }
如下使用
<?php use App\Models\Traits\LikeScope; use Illuminate\Database\Eloquent\Model; class MyModel extends Model { use LikeScope; public function scopeSearch($query, $keyword) { return $query->like('title', $keyword); } }
- 版权申明:此文如未标注转载均为本站原创,自由转载请表明出处《龙行博客》。
- 本文网址:https://www.liaotaoo.cn/375.html
- 上篇文章:Lumen-jwt最详细用户认证
- 下篇文章:唉!到底是转还是不转呢?