发生数据库错误(如果SELECT正常，则SQL_BIG_SELECTS=1或SET MAX_JOIN_SIZE=#)

Question

这个数据库两年来一直运行良好，现在却出现以下错误？有人能解释这个问题并提出解决这个问题的方法吗？

错误编号：1104

SELECT将检查多个MAX_JOIN_SIZE行；检查WHERE，如果SELECT正常，则使用SET SQL_BIG_SELECTS=1或SET MAX_JOIN_SIZE=#

代码：

select 
salary_tb.*, order_tb.order_no, 
daily_target_tb.customer as customer_name, 
jobs_tb.job as job_name 
from 
salary_tb 
left join 
order_tb on salary_tb.order_id = order_tb.id 
left join 
daily_target_tb on order_tb.id = daily_target_tb.order_id 
left join 
jobs_tb on salary_tb.job_id = jobs_tb.id 
where 
salary_tb.id > 0 
and salary_tb.isDeleted = 0 
and salary_tb.employee_id = '1' 
group by 
salary_tb.id

文件名：models/SalaryModel.php

线路编号：22

Answer 1

问题是，您的查询返回的行数过多，超过了为MAX_JOIN_SIZE配置设置的值，或者您的SQL_BIG_SELECTS设置为0，这将不允许您运行花费太长时间才能返回结果的查询。你可以做以下3件事中的一件/全部：

1.SET SQL_BIG_SELECTS=1;

2.MAX_JOIN_SIZE= (More than the no of rows returned by the join statement)

3. 优化查询，使其在设置的参数内以更短的时间返回适当的值。(使用索引，返回较少的行数(

Answer 2

当前查询

function get_rows($where)
{
$sql =      
"select salary_tb.*, order_tb.order_no, daily_target_tb.customer as customer_name, jobs_tb.job as job_name
from salary_tb
left join order_tb on salary_tb.order_id = order_tb.id
left join daily_target_tb on order_tb.id = daily_target_tb.order_id
left join jobs_tb on salary_tb.job_id = jobs_tb.id
where salary_tb.id > 0";

if($where != ""){
$sql .= $where;
}

$query =# $this->db->query('SET SQL_BIG_SELECTS=1'); 
$this->db->query($sql);
return $query->result_array();
}