呆错文章管理系统LIKE查询优化

admin · 发表于 2023-1-10 23:38:05

购买主题本主题需向作者支付 10000 个金币 才能浏览

admin · 发表于 2023-1-11 11:20:01

查询参数实例

$args = [
'cache' => true,
'status' => 'normal',
'action' => 'index',
'controll' => 'detail',
'module' => 'cms',
'field' => config('cms.field_select'),
//'simple' => false,
'sort' => 'info.info_id',
'order' => 'desc',
//'parent' => ['eq',0],
//'user_id' => ['eq',1],
//'id' => ['gt',1000],
//'name' => ['like','%游戏%'],
/*'term_id' => 70,
'meta_key' => ['eq','cms_up'],
'meta_value' => ['gt',1],
'meta_query' =>[
[
'key' =>['eq','cms_letter'],
'value'=>['in','a,b'],
],
],*/
'paginate' => [
'list_rows' => 10,
'page' => $this->site['page'],
]
];

复制代码

生成的SQL语句实例

SELECT `info`.* FROM `dc_info` `info`
INNER JOIN (SELECT `info_id` FROM `dc_info` LIMIT 490,10) tid ON `tid`.`info_id`=`info`.`info_id`
INNER JOIN (SELECT `info_id` FROM `dc_info` WHERE `info_status` = 'normal' AND `info_action` = 'index' AND `info_controll` = 'detail' AND `info_module` = 'cms') tcount ON `tcount`.`info_id`=`info`.`info_id`
INNER JOIN (SELECT `detail_id` FROM `dc_term_map` WHERE `term_id` = 70) tmap ON `tmap`.`detail_id`=`info`.`info_id`
INNER JOIN (SELECT `info_id` FROM `dc_info_meta` WHERE `info_meta_key` = 'cms_up' AND `info_meta_value` > '1') tmeta ON `tmeta`.`info_id`=`info`.`info_id`
INNER JOIN (SELECT `info_id` FROM `dc_info_meta` WHERE `info_meta_key` = 'cms_letter' AND `info_meta_value` = 'a') tmeta0 ON `tmeta0`.`info_id`=`info`.`info_id`
ORDER BY `info`.`info_id` DESC
limit 100,10

复制代码

admin · 发表于 2023-1-11 18:56:48

<?php
namespace app\cms\controller;
use app\common\controller\Front;
class Test extends Front
{
private $info = [];
private $items = [];
public function _initialize()
{
parent::_initialize();
}
public function index()
{
//查询参数
$args = [
'cache' => true,
'status' => 'normal',
'action' => 'index',
'controll' => 'detail',
'module' => 'cms',
'field' => config('cms.field_select'),
//'simple' => false,
'limit' => 10,
'page' => 5,
'sort' => 'info.info_id',
'order' => 'desc',
//'parent' => ['eq',0],
//'user_id' => ['eq',1],
//'id' => ['gt',1000],
//'name' => ['like','%游戏%'],
/*'term_id' => 70,
'meta_key' => ['eq','cms_up'],
'meta_value' => ['gt',1],
'meta_query' =>[
[
'key' =>['eq','cms_letter'],
'value'=>['in','a,b'],
],
],*/
'paginates' => [
'list_rows' => 10,
'page' => $this->site['page'],
]
];
//初始字段
if(config('database.type') == 'mysql'){
$args['field'].=',info.info_id';
}else{
$args['field'] = 'info.*';
}
//$sqlId = db('info')->field('info_id')->where($where)->limit(10)->page(50)->order('info.info_id desc')->fetchSql(true)->select();
$sqlId = db('info')->field('info_id')->limit(10)->page(50)->fetchSql(true)->select();
dump($sqlId);
//联合索引
$sqlFilter = db('info')->field('info_id')->where(DcWhereFilter($args, ['module','controll','action','status','type'], 'eq', 'info_'))->fetchSql(true)->select();
//初始参数
$defaults = array();
$defaults['cache'] = true;
$defaults['group'] = '';
$defaults['fetchSql'] = true;
$defaults['field'] = 'info.*';
$defaults['alias'] = 'info';
$defaults['with'] = 'info_meta,term,user';
$defaults['view'] = [];
$defaults['join'] = [
//['('.$sqlId.') tid','tid.info_id = info.info_id'],
['('.$sqlFilter.') tcount','tcount.info_id = info.info_id'],
];
$defaults['where'] = DcWhereFilter($args, ['id','title','excerpt','password','mime_type','views','hits'], 'eq', 'info_');
$defaults['paginate'] = [];
$defaults['sort'] = 'info.info_id';
$defaults['order'] = 'desc';
//info_parent
if($args['name']){
$sql = db('info')->field('info_id')->where(['info_name'=>DcWhereValue($args['name'],'eq')])->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tname','tname.info_id = info.info_id']);
}
//info_slug
if($args['slug']){
$sql = db('info')->field('info_id')->where(['info_slug'=>DcWhereValue($args['slug'],'eq')])->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tslug','tslug.info_id = info.info_id']);
}
//info_parent
if($args['parent']){
$sql = db('info')->field('info_id')->where(['info_parent'=>DcWhereValue($args['parent'],'eq')])->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tparent','tparent.info_id = info.info_id']);
}
//info_user_id
if($args['user_id']){
$sql = db('info')->field('info_id')->where(['info_user_id'=>DcWhereValue($args['user_id'],'eq')])->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tuser','tuser.info_id = info.info_id']);
}
//info_create_time
if($args['create_time']){
$sql = db('info')->field('info_id')->where(['info_create_time'=>DcWhereValue($args['create_time'],'gt')])->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tcreate','tcreate.info_id = info.info_id']);
}
//info_update_time
if($args['update_time']){
$sql = db('info')->field('info_id')->where(['info_update_time'=>DcWhereValue($args['update_time'],'gt')])->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tupdate','tupdate.info_id = info.info_id']);
}
//term_map
if($args['term_id']){
$sql = db('term_map')->field('detail_id')->where(['term_id'=>DcWhereValue($args['term_id'],'eq')])->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tmap','tmap.detail_id = info.info_id']);
}
//info_meta
if($args['meta_key'] || $args['meta_value']){
$sql = db('info_meta')->field('info_id')->where(DcWhereFilter($args, ['meta_key','meta_value'], 'eq', 'info_'))->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tmeta','tmeta.info_id = info.info_id']);
}
//info_meta_query
if($args['meta_query']){
foreach($args['meta_query'] as $key=>$value){
$sql = db('info_meta')->field('info_id')->where(DcWhereFilter($value, ['key','value'], 'eq', 'info_meta_'))->fetchSql(true)->select();
array_push($defaults['join'],['('.$sql.') tmeta'.$key,'tmeta'.$key.'.info_id = info.info_id']);
}
}
//limit偏移
//分页处理
if(!$args['paginate']){
if($defaults['paginate'] = DcPageFilter($args)){
unset($args['limit']);
unset($args['page']);
}
}
//过滤参数
foreach(['search','id','title','name','slug','excerpt','password','parent','user_id',
'create_time','update_time','views','hits','type','mime_type','status','module','controll','action'] as $key=>$where){
unset($args[$key]);
}
//合并参数
$defaults = DcArrayArgs($args, $defaults);
dump($defaults);
$result = DcDbSelect('common/Info', $defaults);
dump($result);
//dump(DcArrayResult($result));
}
}

复制代码

淡笑 · 发表于 2023-6-14 09:21:38

这是优化数据库的插件吗

		自动登录	找回密码
密码			免费注册

[开发文档] 呆错文章管理系统LIKE查询优化