mysql - SQL_BIG_SELECTS 错误

Question

更新

现在我可以通过删除 ORDER BY 使其工作...不确定这是正确的做法但必须这样做。

---

首先，这是我要加入数据库的所有表中的所有字段(列)

突出显示的字段是我要选择的字段。

所以，我写了 sql 语法来像这样连接所有表

SELECT country_policies.policyname,
       country_policies.countryname,
       section_data_structure.data_name,
       section_data_content_p.dropname,
       section_data_content_p.comment,
       date_format(section_data_content_p.start_date,'%Y-%m-%d') as start_date,
       date_format(section_data_content_p.end_date,'%Y-%m-%d') as end_date,
       section_data_content_p.policy_id,
       section_data_content_p.country_id,
       sections_content.section_id,
       sections_content.statecode
FROM
(
    (country_policies
     INNER JOIN section_data_content_p
         ON country_policies.policyid = section_data_content_p.policy_id AND
            country_policies.countryid = section_data_content_p.country_id
    )
    INNER JOIN section_data_structure
        ON section_data_content_p.data_structure_id = section_data_structure.id
    INNER JOIN sections_content
        ON section_data_content_p.state_id = sections_content.statecode
)
order by section_data_content_p.dropname;

然后我得到了这个错误

ER_TOO_BIG_SELECT: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

所以我尝试添加 SET SQL_BIG_SELECTS= 1 ;像这样到顶部

SET SQL_BIG_SELECTS= 1 ;
SELECT country_policies.policyname, country_policies.countryname, section_data_structure.data_name, section_data_content_p.dropname, section_data_content_p.comment, date_format(section_data_content_p.start_date,'%Y-%m-%d') as start_date, date_format(section_data_content_p.end_date,'%Y-%m-%d') as end_date,section_data_content_p.policy_id,section_data_content_p.country_id,sections_content.section_id,sections_content.statecode
FROM  ((country_policies
INNER JOIN section_data_content_p ON country_policies.policyid = section_data_content_p.policy_id AND country_policies.countryid = section_data_content_p.country_id)
INNER JOIN section_data_structure ON section_data_content_p.data_structure_id = section_data_structure.id
INNER JOIN sections_content ON section_data_content_p.state_id = sections_content.statecode) order by section_data_content_p.dropname;

然后我又报错了

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET SQL_BIG_SELECTS= 1 SELECT country_policies.policyname, country_policies.co' at line 1

还尝试了 SET SESSION/SET OPTION 但都不起作用我仍然无法解决这个问题。 mysql版本是5.6.32

有人可以帮忙吗？谢谢

Answer 1

我整理了您的两个代码示例中的一些括号用法。第一个是现在...

SELECT country_policies.policyname,
       country_policies.countryname,
       section_data_structure.data_name,
       section_data_content_p.dropname,
       section_data_content_p.comment,
       DATE_FORMAT( section_data_content_p.start_date,
                    '%Y-%m-%d' ) AS start_date,
       DATE_FORMAT( section_data_content_p.end_date,
                    '%Y-%m-%d' ) AS end_date,
       section_data_content_p.policy_id,
       section_data_content_p.country_id,
       sections_content.section_id,
       sections_content.statecode
FROM country_policies
     INNER JOIN section_data_content_p ON ( country_policies.policyid = section_data_content_p.policy_id AND country_policies.countryid = section_data_content_p.country_id )
     INNER JOIN section_data_structure ON section_data_content_p.data_structure_id = section_data_structure.id
     INNER JOIN sections_content ON section_data_content_p.state_id = sections_content.statecode
ORDER BY section_data_content_p.dropname;

我还根据 MySQL - SQL_BIG_SELECTS 将 SQL_BIG_SELECT 设置为 session 变量在修改后的第二段代码中，如下...

SET SESSION SQL_BIG_SELECTS = 1;
SELECT country_policies.policyname,
       country_policies.countryname,
       section_data_structure.data_name,
       section_data_content_p.dropname,
       section_data_content_p.comment,
       DATE_FORMAT( section_data_content_p.start_date,
                    '%Y-%m-%d' ) AS start_date,
       DATE_FORMAT( section_data_content_p.end_date,
                    '%Y-%m-%d' ) AS end_date,
       section_data_content_p.policy_id,
       section_data_content_p.country_id,
       sections_content.section_id,
       sections_content.statecode
FROM country_policies
     INNER JOIN section_data_content_p ON ( country_policies.policyid = section_data_content_p.policy_id AND country_policies.countryid = section_data_content_p.country_id )
     INNER JOIN section_data_structure ON section_data_content_p.data_structure_id = section_data_structure.id
     INNER JOIN sections_content ON section_data_content_p.state_id = sections_content.statecode
ORDER BY section_data_content_p.dropname;

注意:我还将布局更改为我发现更易于调试的样式。

如果您有任何问题或意见，请随时发表相应的评论。